Equivalent to Exists function

[FabioManniti]

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

[marcus_sommer · Accepted solution]

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

[FabioManniti]

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;

[barnabyd]

G'day @FabioManniti,

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

Cheers, Barnaby.

[FabioManniti]

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