topic Re: subquery in App Development

subquery

vladimirkup — Tue, 31 Mar 2020 12:37:37 GMT

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

Re: subquery

lorenzoconforti — Tue, 31 Mar 2020 14:47:14 GMT

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

Re: subquery

vladimirkup — Wed, 01 Apr 2020 09:50:47 GMT

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 ?

Re: subquery

lorenzoconforti — Wed, 01 Apr 2020 09:58:22 GMT

Just remove this part:

Drop table filter;

Re: subquery

vladimirkup — Wed, 01 Apr 2020 10:13:52 GMT

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

Re: subquery

lorenzoconforti — Wed, 01 Apr 2020 10:15:42 GMT

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 ;

Re: subquery

vladimirkup — Wed, 01 Apr 2020 10:22:02 GMT

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

Re: subquery

lorenzoconforti — Wed, 01 Apr 2020 10:24:42 GMT

The script I sent you works for me

Can you share yours?

Re: subquery

vladimirkup — Wed, 01 Apr 2020 10:32:02 GMT

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

Re: subquery

lorenzoconforti — Wed, 01 Apr 2020 10:35:00 GMT

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?

Re: subquery

vladimirkup — Wed, 01 Apr 2020 11:23:02 GMT

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

Rowid|Profit

2 | 20

Re: subquery

lorenzoconforti — Wed, 01 Apr 2020 11:30:33 GMT

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?

Re: subquery

vladimirkup — Wed, 01 Apr 2020 11:33:02 GMT

I would need to display multiple rows

Re: subquery

lorenzoconforti — Wed, 01 Apr 2020 13:34:12 GMT

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;

Re: subquery

vladimirkup — Sun, 05 Apr 2020 11:56:06 GMT

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

Thanks a lot !