Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

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

Performance-Opt: ApplyMap instead of long WHERE-statement

Hey,

since I work now since 2 months with QlikView and since my company has way to many QlikView-Reports and facing a problem of overall-run time, I want to focus as much as possible on runtime optimization as possible.

But, since also different users are using the same code, I also want to make the code 'pretty'.

So, here my general question, what you guys think:

classic code: 

          Where col <> 'test' or col <> 'dummy' or col <> 'try';

my idea:

          Where IsNull( ApplyMap(col)  )                     

[I would of course put a map beforehand, where I only map 'test', 'dummy' and 'try' with NULL()]

What do you guys think, would it greatly impact the run time? Would you think the code would be easier to read?

2 Solutions

Accepted Solutions
Highlighted
Contributor II
Contributor II

Ok, I found a nice history-table with 32 mio entries, and set the filter for 3 criteria (result: 170.000 entries).

with match it took 41sec, with mapping 48sec.

So, definitely not worth it.

View solution in original post

Highlighted
MVP & Luminary
MVP & Luminary

If you are loading from QVD, an even better and faster approach is exists()

TempCol:
LOAD * INLINE [
col
test
dummy
try
];

Mydata:
LOAD *
FROM myqvd.qvd (qvd)
Where Not Exists(col);

DROP Table TempCol;

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

View solution in original post

6 Replies
Highlighted
MVP & Luminary
MVP & Luminary

I think the easiest to read and write may be:

Where Match(col, 'test', 'dummy', 'try')
 
I don't guess using a map would be significantly faster or slower, but give it a test if you are curious. 
 
Highlighted
Contributor II
Contributor II

Sorry for the late answer, we had a holiday yesterday, so I didn't look up the post.

Yeah, I was also thinking about match, but I was thinking of storing all parameters central on a tab...

But I just try it out, I was just wondering if someone else already had experience with it.

And thank you!!

Highlighted
Contributor II
Contributor II

Ok, I found a nice history-table with 32 mio entries, and set the filter for 3 criteria (result: 170.000 entries).

with match it took 41sec, with mapping 48sec.

So, definitely not worth it.

View solution in original post

Highlighted
MVP & Luminary
MVP & Luminary

If you are loading from QVD, an even better and faster approach is exists()

TempCol:
LOAD * INLINE [
col
test
dummy
try
];

Mydata:
LOAD *
FROM myqvd.qvd (qvd)
Where Not Exists(col);

DROP Table TempCol;

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

View solution in original post

Highlighted
Contributor II
Contributor II

Cool, thank you, I will try it out!

Highlighted
MVP & Luminary
MVP & Luminary

Wooops. I just noticed that you were testing for not equals.  The where in. my example should include Not

Where Not Exists(col);

-Rob