Multiple values when expanding merged queries.

[lukasjar]

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.Mutliple values for all rows after expanding IdealRunRate from ArticleSettings. MachineId,754_01, article 0, is marked as example tFiltered out the 754_01 machine IDArticleSettings 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!

[Anonymous]

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

[lukasjar · Accepted solution]

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