Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

link table example

I am trying to learn linktable concept.

I found a famous link as below:

Data modeling

And I tried creating dummy data and loaded the script on qvw.

But it shows error saying file not found "-"

I have attached excel and .qvw.

Can someone tell me what is wrong here? Also, I am trying to understand concept of link table. if someone can explain it with example/video that would be great

1 Solution

Accepted Solutions
MarcoWedel

corrected qvw attached

regards

Marco

View solution in original post

3 Replies
MarcoWedel

Hi,

wrong quotation marks and two field name typos.

This script seems to work:

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

Sales:

Load

  Year & '_' & Month & '_' & Branch & '_' & [Item Number] as SalesKey,

  [Customer Number],

  [Invoice Number],

  [Order Number],

  [Salesman Number],

  [Invoice date],

  [Sales Amount],

  [Sales Qty],

  [Cost Amount],

  [Margin Amount],

  [Unit  of Measure]

FROM

LinktableExample.xlsx

(ooxml, embedded labels, table is Sales);

Inventory:

Load

Branch & '_' & [Item Number] as InvKey,

  [On Hand Qty]

FROM

LinktableExample.xlsx

(ooxml, embedded labels, table is Inventory);

Purchasing:

Load

  Year & '_' & Month & '_' & Branch & '_' & [Item Number] as POKey,

  [PO Number],

  [Req Delv Date],

  [PO Amount],

  [Ordered Qty]

FROM

LinktableExample.xlsx

(ooxml, embedded labels, table is Purchasing);

LinkTable:

Load DISTINCT

  Year & '_' & Month & '_' & Branch & '_' & [Item Number] as SalesKey,

Branch & '_' & [Item Number] as InvKey,

  Year & '_' & Month & '_' & Branch & '_' & [Item Number] as POKey,

  Year,

  Month,

  [Branch],

  [Item Number]

FROM

LinktableExample.xlsx

(ooxml, embedded labels, table is Sales);

LinkTable:

Load DISTINCT

Null() & '_' & Null() & Branch & '_' & [Item Number] as SalesKey,

Branch & '_' & [Item Number] as InvKey,

Null() & '_' & Null() & Branch & '_' & [Item Number] as POKey,

Null() as Year,

Null() as Month,

  [Branch],

  [Item Number]

FROM

LinktableExample.xlsx

(ooxml, embedded labels, table is Inventory);

LinkTable:

Load DISTINCT

  Year & '_' & Month & '_' & Branch & '_' & [Item Number] as SalesKey,

Branch & '_' & [Item Number] as InvKey,

  Year & '_' & Month & '_' & Branch & '_' & [Item Number] as POKey,

  Year,

  Month,

  [Branch],

  [Item Number]

FROM

LinktableExample.xlsx

(ooxml, embedded labels, table is Purchasing);

hope this helps

regards

Marco

MarcoWedel

corrected qvw attached

regards

Marco

Not applicable
Author

Thanks Marco.

I am getting the link table concept.

But in below code, we are using null() just because there are no columns as year and month coming in from inventory tab. And to make it uniform with the Saleskey and POKey coming from the sales and Purchasing tab.

And had there been more tabs with common columns we would have created more link table codes like the ones created for sales, inventory and purchasing.

Is that correct interpretation?

LinkTable:

Load DISTINCT

Null() & '_' & Null() & Branch & '_' & [Item Number] as SalesKey,

Branch & '_' & [Item Number] as InvKey,

Null() & '_' & Null() & Branch & '_' & [Item Number] as POKey,

Null() as Year,

Null() as Month,

  [Branch],

  [Item Number]

FROM

LinktableExample.xlsx

(ooxml, embedded labels, table is Inventory);

I am trying to understand what are the other scenarios?

If I had to use concatenate to remove synthetic keys here. How would it be like?