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: 
jim_chan
Specialist
Specialist

How to applymap a 2GB qvd file to load faster? no intention to use Join.

Hi guys!

I have a 2gb QVD that i want to applymap to Fact table. However, it takes 40-50mins to complete the mapping loading. 

How to applymap a 2GB qvd file to load faster? no intention to use Join. Please help.

ABD_MAPPING:
mapping load
MEMBER_KEY & '|' & NUMBER_KEY as MEMBER_NUMBER_UNIQUE_KEY,
DEVICE_KEY & '|' & DEVICE_START_DT as ABC_DEVICE_DATA

FROM E:Z\ABC\DEVICE\ABC_DEVICE.qvd(qvd)
where date(DEVICE_START_DT ) >= '1/1/2018';

rgds

Jim

Labels (1)
4 Replies
agni_gold
Specialist III
Specialist III

Do optimized load for your mapping load table, remove the where clause  and after that take resident of table by applying distinct and order by your key

 

ABD:
load
MEMBER_KEY ,

NUMBER_KEY ,
DEVICE_KEY ,

DEVICE_START_DT

FROM E:Z\ABC\DEVICE\ABC_DEVICE.qvd(qvd)

 

ABD_MAPPING:

mapping load distinct 
MEMBER_KEY & '|' & NUMBER_KEY as MEMBER_NUMBER_UNIQUE_KEY,
DEVICE_KEY & '|' & DEVICE_START_DT as ABC_DEVICE_DATA

Resident ABD order by 

MEMBER_KEY ,

NUMBER_KEY ,
DEVICE_KEY ,

DEVICE_START_DT;

Drop Table ABD;

 

Might this help

jim_chan
Specialist
Specialist
Author

hi agni, i wil try it out

marcus_sommer

Just a small addition. There are use-cases in which a distinct or an order by may be useful but without a certain reason you don't need them and could skip this part from the suggestion from agni_gold because these features will need additional resources and therefore time.

Another run-time reduction might be possible if you could transform your string lookup into a numeric one by using something like this:

MEMBER_KEY * pow(10, 6) + NUMBER_KEY

 The number mustn't be have more as 14 digits and must be of course unique. If it's possible you should try it.

- Marcus

Brett_Bleess
Former Employee
Former Employee

Just adding Design Blog post that may be of some use too:

https://community.qlik.com/t5/Qlik-Design-Blog/Don-t-join-use-Applymap-instead/ba-p/1467592

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.