Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ujjwalraja
Contributor III
Contributor III

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 rows2 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
Partner - Specialist
Partner - Specialist

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
MVP & Luminary
MVP & Luminary

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