BNM_SURVEY: LOAD Account, Curr., SubField(ApplyMap('VENDOR_MASTER_DATA',Account,Null()),'|',1) as [VENDOR NAME], SubField(ApplyMap('VENDOR_MASTER_DATA',Account,Null()),'|',2) as [CTY], "Sum of Amount in doc. curr." as [FAMTCR], "Sum of Amount in local cur." as [LAMTCR], "Sum of Amount in doc. curr.1" as [FAMTDB], "Sum of Amount in local cur.1" as [LAMTDB] FROM [lib://BNM SURVEY/FBL1N-0119-0319.xlsx] (ooxml, embedded labels, header is 2 lines, table is Sheet2);
LOAD Account, 'VENDOR NAME', CTY, Curr., "Sum of FAMTBF" as [FAMTBF], "Sum of LAMTBF" as [LAMTBF] FROM [lib://BNM SURVEY/BF.xlsx] (ooxml, embedded labels, table is FBL1N);
The synthetic (composite) key is a result of multiple common fields. Synthetic keys are often a warning of a poorly designed data model. In this case, you could leave the synthetic key in place, or add a join instruction ahead of the last load to join the data into the fact table. Be aware that a join will add or remove records, depending on the type of join, and if the data field values are misaligned or contain duplicate values of the composite key.
Logic will get you from a to b. Imagination will take you everywhere. - A Einstein