Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mehmet_caldak
Partner - Contributor III
Partner - Contributor III

comparing "where exists" and "applymap"

Hello Community,

i've a qvd has 49 M lines and i need to load it into a qwv file but not all lines just the lines filtered by a certain field. when i considered the possible methods to filter the data, i decided to test "where exists" and "applymap" techniques. firstly i tried  "where exists" and it took more than 17 min to load from qvd, then i tried "applymap", it was amazingly faster, it took just  less than 3 min. i can't understand, why such a big difference occur between these two methods ?

best regards.

6 Replies
magavi_framsteg
Partner - Creator III
Partner - Creator III

Hi mehmet.caldak.

I don't understand how you did it.

Normally, where exists and applymap are used in different scenarios.

Where exists is used to filter data, applymap is "normally" used to substitute data instead of joining it.

ie.

load

field1

,field2

from qvd

where exists(field2)

load

field1

,applymap('MyMap', field2, field2) as field2_description

from qvd

Kind regards

Magnus Åvitsland

BI Consultant

Framsteg Business Intelligence Corp.

mehmet_caldak
Partner - Contributor III
Partner - Contributor III
Author

Hi Magnus Åvitsland,

first of all thank you for your reply.

let me to explain how i applied those scenarios.

in "where exists" i firstly loaded the table that contains the field i want to use for filter. then i pulled the data from my main qvd filtering with "where  exists(field1)"

table1.

load

     field1

from x.qvd where fieldX <> 'A';

table2:

load

field1

field2

field3

......

from main.qvd where exists(field1);

in the second scenario, "applymap", i created a mapping_load table than executed the main load filtered by applymap.

table1:

mapping load

field1

fieldX

from x.qvd;

table2:

Load

field1,

field2,

field3,

....

from main.qvd where applymap('table1',field1) <> 'A';

the scripts are just as like above. did i do something wrong ?

best regards.

alexmosq
Partner - Contributor III
Partner - Contributor III

Clever! Definitely gonna try this.

preminqlik
Specialist II
Specialist II

new idea ..

yassinemhadhbi
Creator II
Creator II

i tried it too , but ther opposite had happened to me.

Where exists is much fater than the where applymap  ...

Best Regards
Yassine Mhadhbi
jonathandienst
Partner - Champion III
Partner - Champion III

This is clearly a case of YMMV - it depends on the cardinality of the exists field among others. So test both in your scenario.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein