Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All
I have tried to join different table and appreciate if anyone confirm whether i have done correctly
Directory Q:\DataSource\Excel\Sales\SalesOrderBook;
For each ExcelFile in filelist ('*.xlsm')
CORE:
Load
[MERCHANDISING MANAGER]AS MANAGER,
BUYER,
[SEASON CODE],
[PHASE/BUY],
[TOP CATERGORIES],
GARMENTS,
[PACK CODE/DESCRIPTION],
PROGRAM,
RANGE,
[PRODUCT DESCRIPTION],
[ITEM CODE],
[PRODUCT CODE],
[COUNTRY CODE],
COUNTRY,
[MODE-ORIGINAL],
[MODE- REVISED],
PO,
ARTICLE,
[BUYER STYLE ID],
[PACK QTY],
[NO OF PACKS],
[ORDER QTY],
[ORDER QTY]/[NO OF PACKS] as PLANNEDPACKS,
[EX FACTORY ORIGINAL],
[EX FACTORY REVISED],
[IHOD/ACTUAL EX FACTORY],
[FOB PO DATE],
[REVISED FOB],
[CRD/VESSEL DATE],
[EX COUNTRY DATE],
[REVISED EX COUNTRY DATE],
NDC,
[SALES DATE]as DATE,
[SALES MONTH],
[FOB-PACK],
COMMISSION,
[FOB WITHOUT COMMISSION],
[TOTAL VALUE AS PER ORDER],
[SHIPPED QTY],
[SHIPPED QTY]/[NO OF PACKS] as SHIPPEDPACKS,
[SHIPMENT VALUE],
[INVOICE NO],
[QTY VARIANCE],
[SHIPPED STATUS],
[PLANNED SMV],
[PLANNED CM],
[PLANNED TOTAL VALUE],
[PLANNED CM/MINUTE],
[PLANNED SAH],
[SALES LOCATION],
[CM PORTION FOR SALES LOCATION],
[QTY FOR PRODUCTION LOCATION],
[KGL-CM],
[TLD-CM],
[TAL-CM],
[PAN-CM],
[PRODUCTION QTY KGL],
[PRODUCTION QTY TLD],
[PRODUCTION QTY TAL],
[PRODUCTION QTY PAN],
[SHIPPED QTY KGL],
[SHIPPED QTY TLD],
[SHIPPED QTY TAL],
[SHIPPED QTY PAN]
From $(ExcelFile) (ooxml, embedded labels, table is [CORE]);
Next ExcelFile;
join
LOAD * INLINE [
COMPANY, SALES LOCATION
EMJAY, KGL
EMJAY, TLD
PENGUIN, TAL
PENGUIN, PAN
];
join
LOAD * INLINE [
COMPANY,BUYER,MANAGER
EMJAY,TESCO OW,HARSHANA
EMJAY,TESCO UW,HARSHANA
EMJAY,H&M,NELUN
EMJAY,LEVIS,NELUN
EMJAY,HUGO BOSS,SUCHITRA/DARREL
EMJAY,DIESEL,SUCHITRA/SHAZRI
EMJAY,LA PERLA,SUCHITRA/SHAZRI
PENGUIN,HELLY HANSEN,NADEEKA
PENGUIN,MERRELL,PIYUMI
PENGUIN,GAP/OLD NAVY,WIDURANGA
PENGUIN,GAP,WIDURANGA
PENGUIN,OLD NAVY,WIDURANGA
];
join
LOAD BUYER,
DATE,
BAMOUNT,
COMPANY,
MANAGER
FROM
Q:\DataSource\Excel\Sales\BUDGET.xlsx
(ooxml, embedded labels, table is Sheet1);
join
LOAD COMPANY,
DATE,
BUYER,
[SHIPMENT VALUE],
BAMOUNT,
MANAGER
FROM
Q:\DataSource\Excel\Sales\SALES20122014.xlsx
(ooxml, embedded labels, table is Sheet1);
That won't work.
I edited your script a little bit (bold text) by putting the link between buyer and company in a separate table. Is this the result you need ?
LOAD * INLINE [
YEAR, BUYER, SALES
2014, TESCO OW, 100
2014, TESCO UW, 100
2014, H&M, 100
2014, LEVIS, 100
2014, HUGO BOSS, 100
2014, DIESEL, 100
2014, LA PERLA, 100
];
join
LOAD YEAR, BUYER, SALES, BUDGET INLINE [
YEAR, BUYER, COMPANY, SALES, BUDGET
2013, TESCO OW, EMJAY, 50, 75
2013, TESCO UW, EMJAY, 50, 75
2013, H&M, EMJAY, 50, 75
2013, LEVIS, EMJAY, 50, 75
2013, HUGO BOSS, EMJAY, 50, 75
2013, DIESEL, EMJAY, 50, 75
2013, LA PERLA, EMJAY, 50, 75
];
join
LOAD * INLINE [
YEAR, BUYER, BUDGET
2014, TESCO OW, 120
2014, TESCO UW, 120
2014, H&M, 120
2014, LEVIS, 120
2014, HUGO BOSS, 120
2014, DIESEL, 120
2014, LA PERLA, 120
];
join
LOAD * INLINE [
BUYER, COMPANY
TESCO OW, EMJAY
TESCO UW, EMJAY
H&M, EMJAY
LEVIS, EMJAY
HUGO BOSS, EMJAY
DIESEL, EMJAY
LA PERLA, EMJAY
];
Hi Jayanthan,
Can you please explain what's your requirement?? based on that we can tell whether this join is correct or not.
Regards,
@vi
I guess the tables from loop will first be concatenated into a single table since they have same column name then the joins will be made on the single concatenated table. So it should work.
-Sundar
Hi All
Thank you for your support
i have attached sample qvw, the issue here is when i select the Company , 2013 year sales are ignored
BR
Jayanthan
You only have values for 2013 for the company "EMJAY" in your data.
So by selecting the company no sales in 2014 are shown.
Hi
However, i have linked Company & Buyer in 2003. In 2004 , isnt the system will not identify the associated company based link created in 2003
BR
Jayanthan
That won't work.
I edited your script a little bit (bold text) by putting the link between buyer and company in a separate table. Is this the result you need ?
LOAD * INLINE [
YEAR, BUYER, SALES
2014, TESCO OW, 100
2014, TESCO UW, 100
2014, H&M, 100
2014, LEVIS, 100
2014, HUGO BOSS, 100
2014, DIESEL, 100
2014, LA PERLA, 100
];
join
LOAD YEAR, BUYER, SALES, BUDGET INLINE [
YEAR, BUYER, COMPANY, SALES, BUDGET
2013, TESCO OW, EMJAY, 50, 75
2013, TESCO UW, EMJAY, 50, 75
2013, H&M, EMJAY, 50, 75
2013, LEVIS, EMJAY, 50, 75
2013, HUGO BOSS, EMJAY, 50, 75
2013, DIESEL, EMJAY, 50, 75
2013, LA PERLA, EMJAY, 50, 75
];
join
LOAD * INLINE [
YEAR, BUYER, BUDGET
2014, TESCO OW, 120
2014, TESCO UW, 120
2014, H&M, 120
2014, LEVIS, 120
2014, HUGO BOSS, 120
2014, DIESEL, 120
2014, LA PERLA, 120
];
join
LOAD * INLINE [
BUYER, COMPANY
TESCO OW, EMJAY
TESCO UW, EMJAY
H&M, EMJAY
LEVIS, EMJAY
HUGO BOSS, EMJAY
DIESEL, EMJAY
LA PERLA, EMJAY
];
hi
yes , thats what i have expected
thank you
BR
Jayanthan
QlikView is not a database - in most cases you do not need to explicitly join tables.
Fields with the same name will automatically associate data in different tables.
Just load the data and get the associations correct without using join, and your scripts will be much simpler.
hi Colin
well noted.Thank you for your valuable information
BR
Jayanthan