Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Experts,
There is something that i do easily in SQL but can't succeed in QLIK Sense. i have a simple following Table1:
rowid | profit
1 30
2 20
3 40
i need to select the row with minimum value in the profit column. so in sql it would be:
select rowid, profit from Table1 where profit = (select min (profit ) from Table1)
and the result would be of course the second row because 20 is the minimum value in the profit column.
but how do i the same thing with the load statement in QLIK sense ?
i tried with WHERE EXISTS or to define a variable with let statement and use it in where clause but nothing works.
Please help
How would you manage the situation where more rows have the minimum value? Would you still display only one row in this third table or multiple rows?
Then the challenge will be synthetic keys and autoconcatenation; to resolve this, I used the qualify statement below; these will give you the 3 tables:
Data:
Load * inline [
rowid, profit
1, 30
2, 20
3, 40
4, 20
];
filter:
Load
1 as isMin,
min(profit) as profit
resident Data
group by ;
left join (Data) load * resident filter;
drop table filter;
qualify *;
MinValueIds:
load * resident Data where (isMin = 1);
MinValue:
Load FirstValue(MinValueIds.isMin) as profit Resident MinValueIds Group by;
The MinValueIds table now has two duplicate rows but except for that it worked.
Thanks a lot !