QLIK DATA MODELING HELP

[ujjwalraja]

HI ALL,

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

2 rows      

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.

 The data model is as follows

[marcohadiyanto]

Hi,

Why don't you link INDENT & PO using PO Number?

do you have sample data and what's KPIs that you want to show?

Regards,

Marco

[marcus_sommer]

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.

- Marcus