Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

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
Highlighted
Partner
Partner

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.

Highlighted
Partner
Partner

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.

Highlighted
Partner
Partner

Clever! Definitely gonna try this.

Highlighted
Specialist II
Specialist II

new idea ..

Highlighted
Contributor III
Contributor III

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

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

Highlighted

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