Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
hi agni, i wil try it out
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
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