Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
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
Highlighted

Re: link table example

corrected qvw attached

regards

Marco

View solution in original post

3 Replies
Highlighted

Re: link table example

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

Highlighted

Re: link table example

corrected qvw attached

regards

Marco

View solution in original post

Highlighted
Not applicable

Re: link table example

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?