Join tables

[#1]

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);

[avinashelite]

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

[sundarakumar]

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

[#4]

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

[geert_gelade]

You only have values for 2013 for the company "EMJAY" in your data.

So by selecting the company no sales in 2014 are shown.

[#6]

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

[geert_gelade · Accepted solution]

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

];

[#8]

hi

yes , thats what i have expected

thank you

BR

Jayanthan

[Colin-Albert]

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.

[#10]

hi Colin

well noted.Thank you for your valuable information

BR

Jayanthan