Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Jul 27, 2024 10:29:39 PM
Jul 27, 2024 10:29:39 PM
Link table example 3
Script:
Sales:
LOAD
Year & '_' & Month & '_' & Branch & '_' & [Item Number] as SalesKey,
//"Year",
//"Month",
// Branch,
//"Item Number",
"Customer Number",
"Invoice Number",
"Order Number",
"Salesman Number",
"Invoice date",
"Sales Amount",
"Sales Qty",
"Cost Amount",
"Margin Amount",
"Unit of Measure"
FROM [lib://DataFiles/LinktableExample.xlsx]
(ooxml, embedded labels, table is Sales);
Inventory:
LOAD
Branch & '_' & [Item Number] as InvKey,
//Branch,
//"Item Number",
"On Hand Qty"
FROM [lib://DataFiles/LinktableExample.xlsx]
(ooxml, embedded labels, table is Inventory);
Purchasing:
LOAD
Year & '_' & Month & '_' & Branch & '_' & [Item Number] as POKey,
//"Year",
//"Month",
//Branch,
// "Item Number",
"PO Number",
"Req Delv Date",
"PO Amount",
"Ordered Qty"
FROM [lib://DataFiles/LinktableExample.xlsx]
(ooxml, embedded labels, table is Purchasing);
/* Create Link Tables */
//Sales
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 [lib://DataFiles/LinktableExample.xlsx]
(ooxml, embedded labels, table is Sales);
//Inventory
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 [lib://DataFiles/LinktableExample.xlsx]
(ooxml, embedded labels, table is Inventory);
//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 [lib://DataFiles/LinktableExample.xlsx]
(ooxml, embedded labels, table is Purchasing);
Hope this help
Vikas