I have 2 tables. INDENT AND PO. I have to show kpis on both Indent Date and PO date, So I created a link table based on the unique keys of both table. Indent table can have multile 0 as Po number as for these Indent , no PO has been issued.Indent table has unique INDENTID. PO table has those PO which has been issued. PO can be issued also without any indent. The load script is as follows
INDENT: LOAD INDENTID, PO, PO&'|'&'CHECK' AS KEY, INDENT_DATE FROM [C:\Users\v-ujjwal.raja\Desktop\INDENT DM TESTING.xlsx] (ooxml, embedded labels, table is INDENT);
PO: LOAD PO, PO&'|'&'CHECK' AS KEY, PO_DATE, PO_AMT FROM [C:\Users\v-ujjwal.raja\Desktop\INDENT DM TESTING.xlsx] (ooxml, embedded labels, table is PO);
LINK_TABLE: lOAD PO, KEY, PO_DATE AS DATE_LINK, 'PO' AS DATATYPE Resident PO;
Concatenate(LINK_TABLE) LOAD INDENTID, PO, KEY, INDENT_DATE AS DATE_LINK, 'INDENT' AS DATATYPE Resident INDENT;
DROP Field PO FROM PO; DROP Field PO,KEY FROM INDENT;
but when I create a Straight table to calculate sum(PO_AMT) with Indent table, I get 2 rows as follows
But when I create a link Table with PO as key I get 1 row, I get single row. (I tested this but this won't fulfill my requirement.)
Here I need to make a composite key to include line item details. Please help me with what should be the right approach.
I think your link-table construct isn't correct because you used different keys - KEY and IDENTID - to associate the tables. Personally I try to avoid link-tables whenever possible and I assume I would rather not use it in this case else I would try to concatenate the tables.