I was trying to join 5 fields using left join to the Master Table. when i was using left join its taking more than 1 hour for QVW reload. Without left join its just taking only 10Minutes time to reload.
help me is there any another way can i join 5 fields to Master Table.
It depends on various parameters which loading-type - resident or per file (qvd) - is more suitable. If the RAM is short and the data are swapped to the virtual RAM it's usually better to store the origin table into a qvd and drop it then and the following steps load then new from the qvd. If your network/storage performance is rather poor you may intentionally use the reverse approach. Also the capabilties of the CPU to process all the loading/transforming will have an impact. This means you need to measure which part is your biggest bottleneck to bypass it as much as possible.
Beside this by your mentioned amount of data you should consider to apply a multi-stage data-architecture and/or to implement an incremental load-logic.
Personally I use very seldom joins especially not if the datasets are rather big and/or there are risks to double/reduce the number of records in regard to the table-relation of the keys respectively to the data-quality. The much better approach is the already mentioned mapping - it's simple and fast, you could define the values of non-matching keys and with concatenated values you could also use a single mapping for multiple fields. Here an example:
map: load KEY, F1 & '|' & F2 & '|' & F3 inline [ KEY, F1, F2, F3 1, a, b, c .... ];
bigTable: load *, subfield(applymap('map', KEY, 'YourDefault1|'), '|', 1) as F1 subfield(applymap('map', KEY, '|YourDefault2'), '|', 2) as F2 subfield(applymap('map', KEY, '||YourDefault'), '|', 3) as F3 from Source;
@marcus_sommer Thanks you so much for your detailed explanation, its tough to understanding for me, below are the listed fields could you please help how to apply the logic. Kindly help me.
Left Join (CRE) LOAD [Load_Date] as [As of Date_Exe], [ID1] as [ID_Exe1], [LoB Name] as [LoB Name_Exe], [Sponsor Name] as [Sponsor Name_Exe], [ID2] as [ ID_Exe2], [Project Title] as [Project Title_Exe], [Commitment Date] as [Commitment Date_Exe], [Sub] as [Sub_Exe], [ Status] as [ Status_Exe]
You need just to regard your order of fields - and of course you need to specify which of the field(s) is your KEY:
map: mapping load KEY, F1 & '|' & F2 & '|' & F3 ....; LOAD [Load_Date] as [F1], [ID1] as [F2], [LoB Name] as [F3], [Sponsor Name] as [F4], [ID2] as [F5], [Project Title] as [F6], [Commitment Date] as [F7], [Sub] as [F8], [ Status] as [F9]
From [..\DataSources\CRE].qvd(qvd);
bigTable: load *, subfield(applymap('map', KEY, 'YourDefault1|'), '|', 1) as [As of Date_Exe], subfield(applymap('map', KEY, '|YourDefault2'), '|', 2) as [ID_Exe1], subfield(applymap('map', KEY, '||YourDefault3'), '|', 3) as [LoB Name_Exe], ... from Source;
i just tried to implement this logic and I got the 'Key' not found error, kindly help me. i am attaching the sample dimension table and Master table. kindly help me
You need of course to adjust my example to your existing fields. Whereby I think there are no common field(s) within both tables and then the data couldn't be merged - regardless which approach (join or mapping) should be used.
