topic Join tables in QlikView

Join tables

Fri, 12 Sep 2014 04:19:56 GMT

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

Re: Join tables

avinashelite — Fri, 12 Sep 2014 04:26:29 GMT

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

Re: Join tables

sundarakumar — Fri, 12 Sep 2014 04:40:03 GMT

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

Re: Re: Join tables

Sat, 13 Sep 2014 12:36:30 GMT

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

Jayanthan

Re: Join tables

geert_gelade — Sat, 13 Sep 2014 18:28:24 GMT

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

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

Re: Join tables

Sat, 13 Sep 2014 18:37:26 GMT

However, i have linked Company & Buyer in 2003. In 2004 , isnt the system will not identify the associated company based link created in 2003

Jayanthan

Re: Join tables

geert_gelade — Mon, 15 Sep 2014 06:59:06 GMT

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

];

Re: Join tables

Mon, 15 Sep 2014 21:08:26 GMT

yes , thats what i have expected

thank you

Jayanthan

Re: Join tables

Colin-Albert — Mon, 15 Sep 2014 21:29:00 GMT

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.

Re: Join tables

Mon, 15 Sep 2014 22:15:44 GMT

hi Colin

well noted.Thank you for your valuable information

Jayanthan