Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

amit200581
New Contributor

Joining multiple tables

Hello Community,

Please help with the following:

I have 3 tables as follows:


Sales1:

Month

CustomerID

CustomerType

Revenue


Sales2:

Month

CustomerAccountNo

CustomerType

Revenue


As observed, the above two transactional tables contain revenue figures per month per customer, but have different customer identifiers (CustomerID is Sales1 and CustomerAccountNo in Sales2). The Month values in Sales1 and Sales2 are same, just that the CustomerType values are different.


In addition, I have two state tables that describe the customers:


Customers:

CustomerID

CustomerAccountNo

CustomerType

CustomerAddress

Age


SpecialCustomers:

CustomerID

CustomerAccountNo

CustomerGroup


Data sets are incomplete, so:

a. Customers table does not contain all Customer info (some SpecialCustomers are not mapped in Customers table)

b. Some Customers in Sales1 and Sales2 are not mapped in Customers

c. All Customers in Customers are not present in Sales1 and Sales2


How do I join (concatenate or link) these tables to have 1 or more associated tables so that I can have filters by CustomerType, Age, CustomerGroup, <other fields>, and present Revenue reports.


I have nulls for certain fields so will have to use set NullValue as well in the script, else few charts will show expression totals against nulls. (Example: Revenue by Age: for cases where a CustomerID is Sales1 but not in Customers will show a number against a Null age)


Thanks for the help.


Cheers

A

Tags (3)
6 Replies
eduardo_dimperi
Valued Contributor II

Re: Joining multiple tables

Can you put some Input and Output example, to make easier define a join?

amit200581
New Contributor

Re: Joining multiple tables

HI Eduardo,

I have enclosed sample data set below:

     

Sales1:
MonthCustomerIDCustomerTypeRevenue
JanID1Retail110
JanID2Retail120
JanID3Retail1320
JanID4Retail1400
Sales2:
MonthCustomerAccountNoCustomerTypeRevenue
JanAC1Retail250
JanAC2Retail260
JanAC3Retail270
JanAC4Retail280
Customers:
CustomerIDCustomerAccountNoCustomerTypeCustomerAddressAge
ID1AC10Retail1ABC21
ID2AC20Retail1DEF26
ID3AC30Retail1GHI28
ID4AC1Retail2JK27
ID5AC2Retail2MN24
ID6AC3Retail2OP22
ID7AC7Retail1EE25
ID8AC8Retail2RR28
SpecialCustomers:
CustomerIDCustomerAccountNoCustomerGroup
ID1AC10G1
ID4AC1G1
ID90AC90G2
ID100AC100G2

As you can observe, the table data is not all inclusive.

The output could be in one of the following ways:

a. A combined table "Sales" with Sales1 and Sales2 data, containing all fields from both tables. Nulls could be marked with <No Value>.

b. A combined table "AllCustomers" with Customers and SpecialCustomers data, identifying the group of the customer. Nulls could be marked with <No Value>.

c. Associative link between Sales and AllCustomers table.

The process may involve intermediate steps and that works for me.

Please advise.

Thanks

eduardo_dimperi
Valued Contributor II

Re: Joining multiple tables

Hi Amit,

Try This

Sales:

Load

*

Resident Sales1

Concatenate (Sales)

Load

*

Resident Sales2

//*******************************************************************

Left Join(Sales)

Load

*

Resident Customers

Left Join(Sales)

Load

*

Resident SpecialCustomers


//*******************************************************************

NoConcatenate

Sales_Table:

LOAD

CustomerID,

CustomerType,

Revenue,

Month,

CustomerAccountNo,

IF(ISNULL(CustomerGroup),'No Value',CustomerGroup) AS CustomerGroup,

IF(ISNULL(CustomerAddress),'No Value',CustomerAddress) AS CustomerAddress,

IF(ISNULL(Age),'No Value',Age) AS Age,

RESIDENT Sales;


Drop table Sales

amit200581
New Contributor

Re: Joining multiple tables

Hi Eduardo,

Thank you for the code snippets, but there is a problem with the joins -

After concatenating Sales1 and Sales2, the Sales table has both CustomerID and CustomerAccNo fields. Now when joining with Customers table, both fields are used to join, resulting in no common values. Hence, I get No Value in the final table.

Cheers

A

eduardo_dimperi
Valued Contributor II

Re: Joining multiple tables

Hi, indeed, but you need to join both fields? Can you supress one of then or change field name?

sumanta12
Contributor II

Re: Joining multiple tables

MAPPING_TAB1:

MAPPING LOAD

CustomerID&'-'&CustomerType AS CUST_ID_TYPE,

    CustomerAccountNo

FROM

(ooxml, embedded labels, table is CUSTOMERS);

MAPPING_TAB2:

MAPPING LOAD

CustomerAccountNo&'-'&CustomerType AS CUST_ACCNO_TYPE,

CustomerID

FROM

(ooxml, embedded labels, table is CUSTOMERS);

===================================================================

TMP_SALES:

LOAD Month,

     CustomerID AS CUST_ID,

     CustomerType AS CUST_TYPE,

     APPLYMAP('MAPPING_TAB1',CustomerID&'-'&CustomerType,'') AS CUST_ACC_NO,

     Revenue

FROM

(ooxml, embedded labels, table is SALES1);

CONCATENATE(TMP_SALES)

LOAD Month,

     CustomerAccountNo AS CUST_ACC_NO,

     CustomerType AS CUST_TYPE,

     APPLYMAP('MAPPING_TAB2',CustomerAccountNo&'-'&CustomerType,'') AS CUST_ID,

     Revenue

FROM

(ooxml, embedded labels, table is SALES2);

NOCONCATENATE

SALES:

LOAD *,

CUST_ID&'-'&CUST_ACC_NO&'-'&CUST_TYPE AS CUST_ID_ACCNO_TYPE

RESIDENT TMP_SALES;

DROP TABLE TMP_SALES;

==============================================================================

TMP_CUSTOMER:

LOAD CustomerID&'-'&CustomerAccountNo AS CUST_ID_ACCNO,

CustomerID,

CustomerAccountNo,

     CustomerType,

     CustomerAddress,

     Age

FROM

(ooxml, embedded labels, table is CUSTOMERS);

JOIN(TMP_CUSTOMER)

LOAD CustomerID&'-'&CustomerAccountNo AS CUST_ID_ACCNO,

     CustomerGroup

FROM

(ooxml, embedded labels, table is [SPECIAL CUSTOMERS]);

NOCONCATENATE

CUSTOMER:

LOAD *,

CustomerID&'-'&CustomerAccountNo&'-'&CustomerType AS CUST_ID_ACCNO_TYPE

RESIDENT TMP_CUSTOMER;

DROP TABLE TMP_CUSTOMER;