topic Re: Set Analysis within Set Analysis in QlikView

Set Analysis within Set Analysis

ManuelRühl — Sat, 16 Nov 2024 03:55:46 GMT

I've got the following problem:

I need the price of a materail which is Status=1 and is the lowest price with the lowest priority.

I tried everything and searched through the community but I didn't find the right solution.

Thanks in advance for your help!

For example:

Input:
LOAD * INLINE [
Material, Priority, Price, Status
A, 1, 30, 2
A, 2, 20, 1
A, 2, 30, 1
A, 3, 10, 3
A, 1, 20, 1
B, 3, 40, 1
B, 2, 70, 5
B, 1, 80, 1
B, 1, 50, 1
];

Re: Set Analysis within Set Analysis

stevedark — Sat, 06 Apr 2019 21:23:58 GMT

Does it need to respond to selections when identifying the lowest priority?

If not, I would look at doing something in the load script like this:

MyData:
LOAD
Material &'-'& num(Priority, '0000') as MaterialPriorityKey,
Material,Priority,Status
etc...

LOAD
1 as IsLowest,
MinString(MaterialPriorityKey)
RESIDENT MyData
WHERE Status = 1
GROUP BY Material
;

That would then give you a flag you could then use in Set Analysis, like this: {<IsLowest={1}>}

If you wanted it to be dependent on selections, so if you selected Priorty 2 it would then give you that price as it would then be the lowest it gets a bit more messy. Let me know if you need to go there...

Steve

Re: Set Analysis within Set Analysis

ManuelRühl — Sun, 07 Apr 2019 15:59:04 GMT

Thank you very much for your answer! In this case, it would be fine to do so in the script. But the main question for me and behind this post is: How can I do set analysis which includes dynamic selections per line? Another requirement is, to sum up the average turnover of the last 5 orders of a customer. Of course I could solve that within the script as well, but then it doesn't depend on the actual selection any more. Can you give ma hint?

Re: Set Analysis within Set Analysis

Gysbert_Wassenaar — Sun, 07 Apr 2019 16:26:23 GMT

With your example data this should do the trick: FirstSortedValue({<Status={1}>}Price, Priority*1000000+Price)

Re: Set Analysis within Set Analysis

stevedark — Mon, 08 Apr 2019 07:42:12 GMT

As ever, @Gysbert_Wassenaar has given a great solution for this. Just to add that if you want to get a total of that expression you will need to use an AGGR. You are perhaps unlikely to want to get a total price, but if you want a total value it would look like this:

sum(aggr(FirstSortedValue({<Status={1}>}Price, Priority*1000000+Price) * Quantity,Material))

If you use this in a table which has Price, Priority and Status in the value will appear against an arbitrary row, rather than against the one which has the Status and the lowest Priority. If you are using this expression ensure you don't have those other fields as dimensions.

The section on AGGR on help.qlik.com should illuminate why the above is needed and works.

Hope that helps.

Re: Set Analysis within Set Analysis

qliksus — Mon, 08 Apr 2019 09:53:54 GMT

Creating a field like

load * ,
Priority & Status as Key
from table ;

and use expression min(TOTAL<Material> {<Key={'11'}>}Price)

Re: Set Analysis within Set Analysis

ManuelRühl — Mon, 08 Apr 2019 13:31:15 GMT

Thanks a lot for all the solutions!

The answer of@Gysbert_Wassenaar was the easiest one, but the other one without scripting provided by @stevedark worked as well!

I try to get on with it with my problem summing up the last 5 orders per customer.

Re: Set Analysis within Set Analysis

stevedark — Mon, 08 Apr 2019 18:12:07 GMT

The important difference between the two solutions is what happens when selections are made. Test to ensure you get the solution you want.