link table example

[#1]

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

[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 · Accepted solution]

corrected qvw attached

regards

Marco

[#4]

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?