topic Joining table on multiple keys - how to in QlikView

Joining table on multiple keys - how to

agaetisproject — Tue, 18 Aug 2015 20:26:35 GMT

Hi all,

I am trying to expand my script capabilities with joining tables.

I am a real dummy here and would appreciate guidance on the following case.

I have two tables:

Table 1: A table giving the actual sales by country, product and year

Table 2: A table giving the potential sales by country, by product (not year as potential is market size) and the sales forecast by country, by product and by year

My goal is to create a join in script so I get the table OUTPUT with all data (actual sales, potential sales and sales forecast) assembled that I can then easily use to make charts.

Can anyone help me come up with the syntax?

Getting this would help me understand how to join and help me progress further in creating QlikSense apps 🙂

I also attach an Excel file to make it easier for you to help me.

Thanks in advance!

Table 1:

CountryCode	ProductName	ClientID	ClientName	Billing Date	Curr.	TotalBillValue	Bill. Qty in SKU	SalesMan
AU	Product1	100	ABC	10/03/2015	EUR	1,000.00	1	John
AU	Product2	100	ABC	10/03/2015	EUR	100.00	1	John
AU	Product1	101	EFG	10/03/2015	EUR	1,000.00	1	John
AU	Product2	101	EFG	10/03/2015	EUR	100.00	1	John
AT	Product1	102	HIJ	10/03/2015	EUR	2,000.00	2	Henry
AT	Product2	102	HIJ	10/03/2015	EUR	300.00	3	Henry
BA	Product1	103	KLM	10/03/2015	EUR	5,000.00	5	Henry
BA	Product2	103	KLM	10/03/2015	EUR	200.00	2	Henry

Table2

CountryCode	CountryName	ProductName	CountryPotential	BillingYear	SalesForecast
AU	Australia	Product1	30	2015	12
AU	Australia	Product1	30	2016	15
AU	Australia	Product2	20	2015	12
AU	Australia	Product2	20	2016	15
AT	Austria	Product1	24	2015	8
AT	Austria	Product1	24	2016	10
BA	Bosnia and Herzegovina	Product2	3	2015	1
BA	Bosnia and Herzegovina	Product2	3	2016	2

OUTPUT (wished)

CountryCode	ProductName	ClientID	ClientName	Billing Date	Curr.	TotalBillValue	Bill. Qty in SKU	SalesMan	BillingYear	CountryPotential	SalesForecast
AU	Product1	100	ABC	10/03/2015	EUR	1,000.00	1	John	2015	30	12
AU	Product2	100	ABC	10/03/2015	EUR	100.00	1	John	2015	30	15
AU	Product1	101	EFG	10/03/2015	EUR	1,000.00	1	John	2015	20	12
AU	Product2	101	EFG	10/03/2015	EUR	100.00	1	John	2015	20	15
AT	Product1	102	HIJ	10/03/2015	EUR	2,000.00	2	Henry	2015	24	8
AT	Product2	102	HIJ	10/03/2015	EUR	300.00	3	Henry	2015	24	10
BA	Product1	103	KLM	10/03/2015	EUR	5,000.00	5	Henry	2015	3	1
BA	Product2	103	KLM	10/03/2015	EUR	200.00	2	henry	2015	3	2

Re: Joining table on multiple keys - how to

Tue, 18 Aug 2015 20:34:29 GMT

You can simply use Join (Left, Inner, Outer ,Right -- Optional) key word to join the tables and make sure that the key fields must be same name. Please find below:

DATA:

LOAD

CountryCode,

ProductName,

ClientID,

ClientName,

[Billing Date],

[Curr.],

TotalBillValue,

[Bill. Qty in SKU],

SalesMan

From Excelfile;

Join(DATA)

LOAD

CountryCode,

CountryName,

ProductName,

CountryPotential,

BillingYear,

SalesForecast

From Excelfile;

In the above example the tables joined on CountryCode & ProductName.

Please go to help file to find out more on the Join

Re: Joining table on multiple keys - how to

qlikviewwizard — Wed, 19 Aug 2015 02:10:06 GMT

Hi,

Please use the below code and check the attached sample file.

Thank you.

Table1:

LOAD * INLINE [

CountryCode,ProductName,ClientID,ClientName,Billing Date,Curr.,TotalBillValue,Bill. Qty in SKU,SalesMan

AU,Product1,100,ABC,10/03/2015,EUR,1,000.00,1,John

AU,Product2,100,ABC,10/03/2015,EUR,100.00,1,John

AU,Product1,101,EFG,10/03/2015,EUR,1,000.00,1,John

AU,Product2,101,EFG,10/03/2015,EUR,100.00,1,John

AT,Product1,102,HIJ,10/03/2015,EUR,2,000.00,2,Henry

AT,Product2,102,HIJ,10/03/2015,EUR,300.00,3,Henry

BA,Product1,103,KLM,10/03/2015,EUR,5,000.00,5,Henry

BA,Product2,103,KLM,10/03/2015,EUR,200.00,2,

Henry

];

JOIN (Table1)

Table2:

LOAD * INLINE [

CountryCode,CountryName,ProductName,CountryPotential,BillingYear,SalesForecast

AU,Australia,Product1,30,2015,12

AU,Australia,Product1,30,2016,15

AU,Australia,Product2,20,2015,12

AU,Australia,Product2,20,2016,15

AT,Austria,Product1,24,2015,8

AT,Austria,Product1,24,2016,10

BA,Bosnia and Herzegovina,Product2,3,2015,1

BA,Bosnia and Herzegovina,Product2,3,2016,

];

Re: Joining table on multiple keys - how to

jagan — Wed, 19 Aug 2015 04:18:52 GMT

Hi Jeff,

You should not join both the tables it will duplicate the records, so you have to go with the Linktable like below

Table1:

LOAD

AutoNumber(CountryCode & ProductName) AS Key,

INLINE [

CountryCode,ProductName,ClientID,ClientName,Billing Date,Curr.,TotalBillValue,Bill. Qty in SKU,SalesMan

AU,Product1,100,ABC,10/03/2015,EUR,1,000.00,1,John

AU,Product2,100,ABC,10/03/2015,EUR,100.00,1,John

AU,Product1,101,EFG,10/03/2015,EUR,1,000.00,1,John

AU,Product2,101,EFG,10/03/2015,EUR,100.00,1,John

AT,Product1,102,HIJ,10/03/2015,EUR,2,000.00,2,Henry

AT,Product2,102,HIJ,10/03/2015,EUR,300.00,3,Henry

BA,Product1,103,KLM,10/03/2015,EUR,5,000.00,5,Henry

BA,Product2,103,KLM,10/03/2015,EUR,200.00,2,Henry

];

LinkTable:

LOAD

DISTINCT Key, CountryCode,ProductName

RESIDENT Table1;

Table2:

LOAD

AutoNumber(CountryCode & ProductName) AS Key,

INLINE [

CountryCode,CountryName,ProductName,CountryPotential,BillingYear,SalesForecast

AU,Australia,Product1,30,2015,12

AU,Australia,Product1,30,2016,15

AU,Australia,Product2,20,2015,12

AU,Australia,Product2,20,2016,15

AT,Austria,Product1,24,2015,8

AT,Austria,Product1,24,2016,10

BA,Bosnia and Herzegovina,Product2,3,2015,1

BA,Bosnia and Herzegovina,Product2,3,2016,2

];

Concatenate(LinkTable)

LOAD

DISTINCT Key, CountryCode,ProductName

RESIDENT Table2;

DROP FIELDS CountryCode,ProductName FROM Table1, Table2;

Hope this helps you.

Regards,

Jagan.

Re: Joining table on multiple keys - how to

agaetisproject — Wed, 19 Aug 2015 06:58:33 GMT

Hi Jagan,

Thank you. I just have an error with the following statement

Any idea what happens there?

Concatenate(Linktable)

The following error occurred:

Unknown statement

The error occurred here:

Concatenate(LinkTable) LoadDistinct Key, [Country Key], [Material Number] Resident Table2

Re: Joining table on multiple keys - how to

qlikviewwizard — Wed, 19 Aug 2015 08:00:05 GMT

Hi,

Please use this script. This is working for me.

Table1:

LOAD *,

AutoNumber(CountryCode & ProductName) AS Key;

LOAD CountryCode,

ProductName,

ClientID,

ClientName,

[Billing Date],

Curr.,

TotalBillValue,

[Bill. Qty in SKU],

SalesMan

FROM

[Potential and forecast - QlikCommunity.xlsx]

(ooxml, embedded labels, table is Table1);

LinkTable:

LOAD

DISTINCT Key, CountryCode,ProductName

RESIDENT Table1;

Table2:

LOAD *,

AutoNumber(CountryCode & ProductName) AS Key;

LOAD CountryCode,

CountryName,

ProductName,

CountryPotential,

BillingYear,

SalesForecast

FROM

[Potential and forecast - QlikCommunity.xlsx]

(ooxml, embedded labels, table is Table2);

Concatenate(LinkTable)

LOAD

DISTINCT Key, CountryCode,ProductName

RESIDENT Table2;

Re: Joining table on multiple keys - how to

jagan — Wed, 19 Aug 2015 09:31:14 GMT

Hi,

I am not getting any error, please find attached file and reload.

Regards,

Jagan.

Re: Joining table on multiple keys - how to

sasiparupudi1 — Thu, 20 Aug 2015 12:46:07 GMT

Please try the attachment

hth

Sasi

qualify CountryCode,ProductName;

Table1:

LOAD

CountryCode&'-'& ProductName&'-'&year(date#([Billing Date],'DD/MM/YYYY')) as Key,

* INLINE [

CountryCode,ProductName,ClientID,ClientName,Billing Date,Curr.,TotalBillValue,Bill. Qty in SKU,SalesMan

AU,Product1,100,ABC,10/03/2015,EUR,"1000.00",1,John

AU,Product2,100,ABC,10/03/2015,EUR,"100.00",1,John

AU,Product1,101,EFG,10/03/2015,EUR,"1000.00",1,John

AU,Product2,101,EFG,10/03/2015,EUR,"100.00",1,John

AT,Product1,102,HIJ,10/03/2015,EUR,"2000.00",2,Henry

AT,Product2,102,HIJ,10/03/2015,EUR,"300.00",3,Henry

BA,Product1,103,KLM,10/03/2015,EUR,"5000.00",5,Henry

BA,Product2,103,KLM,10/03/2015,EUR,"200.00",2,Henry

];

JOIN (Table1)

Table2:

LOAD *,CountryCode&'-'& ProductName&'-'&BillingYear as Key INLINE [

CountryCode,CountryName,ProductName,CountryPotential,BillingYear,SalesForecast

AU,Australia,Product1,30,2015,12

AU,Australia,Product1,30,2016,15

AU,Australia,Product2,20,2015,12

AU,Australia,Product2,20,2016,15

AT,Austria,Product1,24,2015,8

AT,Austria,Product1,24,2016,10

BA,Bosnia and Herzegovina,Product2,3,2015,1

BA,Bosnia and Herzegovina,Product2,3,2016,2

];

NoConcatenate

final:

LOAD * Resident Table1 where isnull(ClientID)=0;

drop table Table1;

Re: Joining table on multiple keys - how to

qlikviewwizard — Thu, 20 Aug 2015 13:56:37 GMT

Did you able to load the data?