Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
agaetisproject
New Contributor III

Joining table on multiple keys - how to

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:

         

CountryCodeProductNameClientIDClientNameBilling DateCurr.TotalBillValueBill. Qty in SKUSalesMan
AUProduct1100ABC10/03/2015EUR1,000.001John
AUProduct2100ABC10/03/2015EUR100.001John
AUProduct1101EFG10/03/2015EUR1,000.001John
AUProduct2101EFG10/03/2015EUR100.001John
ATProduct1102HIJ10/03/2015EUR2,000.002Henry
ATProduct2102HIJ10/03/2015EUR300.003Henry
BAProduct1103KLM10/03/2015EUR5,000.005Henry
BAProduct2103KLM10/03/2015EUR200.002

Henry

Table2

      

CountryCodeCountryNameProductNameCountryPotentialBillingYearSalesForecast
AUAustraliaProduct130201512
AUAustraliaProduct130201615
AUAustraliaProduct220201512
AUAustraliaProduct220201615
ATAustriaProduct12420158
ATAustriaProduct124201610
BABosnia and HerzegovinaProduct2320151
BABosnia and HerzegovinaProduct232016

2

OUTPUT (wished)

           

CountryCodeProductNameClientIDClientNameBilling DateCurr.TotalBillValueBill. Qty in SKUSalesManBillingYearCountryPotentialSalesForecast
AUProduct1100ABC10/03/2015EUR1,000.001John20153012
AUProduct2100ABC10/03/2015EUR100.001John20153015
AUProduct1101EFG10/03/2015EUR1,000.001John20152012
AUProduct2101EFG10/03/2015EUR100.001John20152015
ATProduct1102HIJ10/03/2015EUR2,000.002Henry2015248
ATProduct2102HIJ10/03/2015EUR300.003Henry20152410
BAProduct1103KLM10/03/2015EUR5,000.005Henry201531
BAProduct2103KLM10/03/2015EUR200.002henry201532
8 Replies
Not applicable

Re: Joining table on multiple keys - how to

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

Arjunarao
Honored Contributor II

Re: Joining table on multiple keys - how to

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,

2

];

MVP
MVP

Re: Joining table on multiple keys - how to

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.

agaetisproject
New Contributor III

Re: Joining table on multiple keys - how to

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

Arjunarao
Honored Contributor II

Re: Joining table on multiple keys - how to

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;

MVP
MVP

Re: Joining table on multiple keys - how to

Hi,

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

Regards,

Jagan.

sasiparupudi1
Honored Contributor III

Re: Joining table on multiple keys - how to

Please try the attachment

hth

Sasi

Untitled2.jpg

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;

Arjunarao
Honored Contributor II

Re: Joining table on multiple keys - how to

Did you able to load the data?

Community Browser