Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

ujjwalraja
Not applicable

QLIK DATA MODELING HELP

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.PNG2 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 followsDATA_MODEL_INDENT_PO.PNG

 

 

2 Replies
chooco_co
Not applicable

Re: QLIK DATA MODELING HELP

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

Highlighted
marcus_sommer
Not applicable

Re: QLIK DATA MODELING HELP

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