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

[lorenzoconforti]

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

[vladimirkup]

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 ?

[lorenzoconforti]

Just remove this part:

Drop table filter;

[vladimirkup]

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

[lorenzoconforti]

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 ;

[vladimirkup]

No good. Now Data table is unchanged, but the inner keep didn't work. Probably because the name of the fields is not identical

[lorenzoconforti]

The script I sent you works for me

Can you share yours?

[vladimirkup]

I am running your script.

I copy and paste your script to my Qlik Sence load editor, then press load button, go to my preview tab in model viewer and see thta both tables (Data table and filter table) are unchanged which is good. But I also don't see the resulted filtered row in a new table

[lorenzoconforti]

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?