Is lookup() function expensive?

[boorgura]

Dear Qlikers,

Is LOOKUP() function expensive in terms of performance?

Does it slow down the reload drastically?

Replies would be highly appreciated.

[Oleg_Troyansky]

Rocky,

I tried lookup() a few years (a few major releases) ago, and at that time it was very expensive and sometimes unstable. I quickly gave up and replaced it completely with mapping, which had proven to be very fast and reliable.

There might have been recent improvements in the performance of a lookup(), but I'd intuitively think that it's always going to be slower than mapping.

just my two cents...

[boorgura]

Thanks for your response Oleg.

But, my situation here is:

I have a given date - which is considered as the base date.

In the table, I created a combination key of fields - for unique identity.

Now, I should trend only those values, which were present on the base date.

Say 1/1/2010 was the base date and there were 1000 records.

Trend should show only those 1000 records for all the dates. even if some additional records come in they should be ignored.

Let me know if you can think of a possible solution.

[Oleg_Troyansky]

I'm not sure I totally understand your problem, and how it's related to lookup()...

How do you differentiate your "base" 1000 records from all the others?

Or, maybe let's approach it this way - explain to me how lookup() could solve your problem, and I'll try to assess if there is a better way...

[boorgura]

I have created a field s_Key which is a concatenated field - for unique identification.

so now on the base date I have 1000 s_Key values.

So on any other given date, if there is a record which has the s_Key value as one of the above 1000, it should be flagged 'Y' else 'N'.

obviously on the base date, everything will be flagged 'Y'.

So I created a intermittent resident table with the s_Key, s_Flag and s_Date fields.

and in the final load I used this conditional field:

if(isnull(lookup('Matcher.s_Flag', 'Matcher.s_Key', s_Key, 'Matcher')), 'N', 'Y') as [s_Flag],

where Matcher is my intermittent Table.

I think i explained a bit better now.

sorry if I messed up!! 🙂

[Oleg_Troyansky]

Yes, I think I understand... You can do exactly the same with Mapping:

- create a temporary Mapping table using Mapping load

- Use function ApplyMap() instead of Lookup

- the rest of the logic is exactly the same.

I think you'll find it performaing faster...

[boorgura]

Thanks Oleg.

I really appreciate your time.

I will try out this approach first thing tomorrow. and will let you know if it worked or not!!!