Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
];
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
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.
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.
The important difference between the two solutions is what happens when selections are made. Test to ensure you get the solution you want.