Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
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.

4 Replies
Partner
Partner

Re: comparing "where exists" and "applymap"

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.

Partner
Partner

Re: comparing "where exists" and "applymap"

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.

Partner
Partner

Re: comparing "where exists" and "applymap"

Clever! Definitely gonna try this.

preminqlik
Valued Contributor II

Re: comparing "where exists" and "applymap"

new idea ..