Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am trying to learn linktable concept.
I found a famous link as below:
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
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
corrected qvw attached
regards
Marco
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?