subquery

[vladimirkup]

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

[vladimirkup]

Yes, I would like to see in the 3rd table the selected row. In our case that would be:

Rowid|Profit

2 | 20

[lorenzoconforti]

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?

[vladimirkup]

I would need to display multiple rows

[lorenzoconforti · Accepted solution]

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;

[vladimirkup]

The MinValueIds table now has two duplicate rows but except for that it worked.

Thanks a lot !