Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
lukasjar
Resolver I
Resolver I

Multiple values when expanding merged queries.

Hello Community!

I have issues when expanding one value from merging queries. Ut causes several duplicates on my grouped rows. I have done a pivoted column and I have only one value per Machine/Article in the ArticleSettings table where I have the value im merging.

 

What I want to happen is to:

1. Get the IdealRunRate value for each row in Table "OeeDataAgg"

2. I then want to add a column to get the IdealProducedItemsSum (to compare to OeeDataAgg_ProducedItemsS...). This is a simpler multiply task (AutoHoursSum * IdealRunRate) and i would be able to calculate a performance ratio for that Machine/Article/Day.

 

 

The table before expanding ArticleSettings. filtering MachineId 754_01 for future reference.The table before expanding ArticleSettings. filtering MachineId 754_01 for future reference.Mutliple values for all rows after expanding IdealRunRate from ArticleSettings. MachineId,754_01, article 0, is marked as example tMutliple values for all rows after expanding IdealRunRate from ArticleSettings. MachineId,754_01, article 0, is marked as example tFiltered out the 754_01 machine IDFiltered out the 754_01 machine IDArticleSettings without filter.ArticleSettings without filter.

 

 

If anyone is used to QlikView i have used the function below to calculate the performance.

Don't actually think this will help but I guessi it's better putting it out there then not since I am not sure.

 

sum(aggr(
(sum(ProducedItems)/(sum(IdealRunRate)*sum(RunTime)))*sum(RunTime)
,ArticleNumber,Machine,StartTimeOEE)
)/sum(RunTime)

 

Tank you in advance for any support!

1 ACCEPTED SOLUTION

I found that when merging queries I can use several columns to match data, solved my issue!

 

image.png

View solution in original post

2 REPLIES 2
Hitesh-Dsouza
Regular Visitor

This typically happens when the columns you are joining on has duplicates. If the column (you are joining/merging on) has duplicates, it will result in a cross join and give multiple values. Check the joining column for duplicate values. There should be unique values in such columns

I found that when merging queries I can use several columns to match data, solved my issue!

 

image.png

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors