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
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;
You can't do it straight on your first load; you need to load the data first and then the get min value
Data:
Load * inline [
rowid, profit
1, 30
2, 20
3, 40
];
filter:
inner keep load
min(profit) as profit
resident Data
group by ;
Drop table filter;
Taken from here:
https://community.qlik.com/t5/QlikView-Scripting/Load-Record-with-Max-value-only/td-p/696289
Hi,
Thanks, it worked great except that the original table Data was changed and left only with min value row.
Is there a way to rewrite the load statement in a way that the Data table will remain unchanged and the resulted row with the min value will be loaded to a new table ?
Just remove this part:
Drop table filter;
Did it but that kept the filter table from deleting and the Data table is still with the min value row only.
I am looking for a way keep the Data table from being changed and insert min value row to a new table
Try renaming the field:
Data:
Load * inline [
rowid, profit
1, 30
2, 20
3, 40
];
filter:
inner keep load
min(profit) as minProfit
resident Data
group by ;
The script I sent you works for me
Can you share yours?
So, do you want to see three tables?
Data (unchanged)
Filter (with only one value)
3rd Table - what would you like to see in here? Data table with just one row?