topic Re: Equivalent to Exists function in App Development

Equivalent to Exists function

FabioManniti — Thu, 17 Nov 2022 12:38:30 GMT

Hello

In my editor I have imported two tables from my Database: wallet and movents.

In the movements table I can find a field which tells me the type of movement.

Into my wallet table I want to add a field which tells me whether for that wallet exists a certain type of movement or not.

If I had to do it by MySQL Query, I would use this and I would like to know if is there anything similar in QLik editor.

Thank you

SELECT w.*, EXISTS( SELECT 1 FROM movements m WHERE m.wallet_id = w.id AND m.type = 'BUY' OR m.type = 'SELL' )AS has_exchange_movement FROM wallet w

Re: Equivalent to Exists function

marcus_sommer — Thu, 17 Nov 2022 13:05:51 GMT

Depending on your scenario you may keep this sql-statement as it is within Qlik because Qlik doesn't execute any sql itself else just transferring the statement per driver to the database and receiving on the same way back the results.

Beside this Qlik has an own exists() function but it will work differently to your shown example. I think I would try to solve your task with a mapping-approach, maybe something like the following:

m: mapping load wallet_id, 1;
sql select wallet_id from movements
where type = 'BUY' or type = 'SELL';

load *, applymap('m', id, 0) as has_exchange_movements;
sql select * from wallet;

- Marcus

Re: Equivalent to Exists function

FabioManniti — Thu, 17 Nov 2022 14:59:24 GMT

Thank you. I will try this solution to see if it works.

I also did this which I must say it works:

wallet:

Load *

From wallet;

exchange:

Load distinct wallet_id, 1 as has_exchange

From Movements

Where type = 'BUY' or type = 'SELL'

wallet_final

Load *

resident wallets

Left join *

resident exchange;

drop table wallet;

drop table exchange;

Re: Equivalent to Exists function

barnabyd — Thu, 17 Nov 2022 20:50:32 GMT

G'day @FabioManniti,

Your solution will work fine but for large data sets the applymap() function is much more efficient.

Cheers, Barnaby.

Re: Equivalent to Exists function

FabioManniti — Mon, 21 Nov 2022 15:27:13 GMT

Thank you for the advice. I must admit this is way faster