If you can map the model name to the part number, I would recommend creating a map table (look for "Mapping Load") and then apply the map (look for applymap()) to the field. You would need to identify which CRM records are by PN and which are by Model and you would have the 1 key instead of 2. For example,
Load CRMkey & '_' &
if(CRMDate<=makedate(2008,1,1), applymap('PN_Model_Map',PartNumber), PartNumber) as InvoiceKey,
Or if you don't need a mapping just do the following:
Load CRMkey & '_' &
if(CRMDate<=makedate(2008,1,1), Model, PartNumber) as InvoiceKey,
Finally, if you don't know which invoices or CRM records contain which key, I would do something like what you are thinking. I would load one of the tables and do an inner join with the other table with the PN as the key and then concatenate that table with an inner join of the same 2 tables with the model as key. The 2 keys will have the same field name. At the end if not all CRM records have invoices and not all invoices have CRM records you'll have to concatenate the CRM records and Invoices that don't exist in the joined table. You could use the exists() function to load records from the 2 tables and don't exist in the joined table.
I hope that gives you some direction.
I will have to try your final solution, because indeed I don't know which fields have an invoice, and which don't.
CRM is my main table, the invoices come from a huge database which would not like to load twice from it's source, so I guess I could use resident or QVD?
I'm going to give this a whirl and come back with succes or fail. Thanks much in advance!
Once more. I've forgot to add that the last 3 digits of MODEL are always the same.
so a PN would be 7 Characters (numeric or alpha) but a MODEL is always 4x Numeric and ending with ABC.
If I load CRM with the IF
if(right(PNCRM,3)="ABC", Model, PN) as InvoiceKey,
Where Model keys against Model in INVOICE and PN against PN in INVOICE.
Will Qlikview create the SynLinks because we are matching 2 keys in one load?
Final ( Working ) model (simplified)
INVOICE: LOAD PN, MTM, QTYSM FROM [Book1.xlsx] (ooxml, embedded labels, table is INVOICE); //FIRST LOAD CRM1: inner join (INVOICE) LOAD PNCRM as PN, QTY FROM [Book1.xlsx] (ooxml, embedded labels, table is CRM); INVOICE1: NoConcatenate LOAD PN, MTM, QTYSM FROM [Book1.xlsx] (ooxml, embedded labels, table is INVOICE); //SECOND LOAD CRM2: inner join (INVOICE1) LOAD PNCRM as MTM, QTY FROM [Book1.xlsx] (ooxml, embedded labels, table is CRM); Final: Concatenate (INVOICE) Load PN, QTYSM, QTY, MTM resident INVOICE1; drop table INVOICE1;