Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

cancel
Showing results for 
Search instead for 
Did you mean: 
Correct_Answer
Contributor II
Contributor II

I am trying to join 2 datasets but facing an issue with key

Hello All,

I am trying to compare customer sales on 2 different dates, 3/2/2021 and 3/1/2021.

below are the data sets ,

DataSet1:

//(03-02-2021)

CustomerIDSalesTypeSalesDateSalesAmountSalesChangeDateYear
15108Self7/30/2021429803-02-20212022
15108Self7/30/2021003-02-20212022
15108Self7/30/2021431203-02-20212022
15108Self7/30/2021003-02-20212022
15108Self7/30/20214298.103-02-20212022

 

and 

DataSet2:

//03-/01-2021

CustomerIDSalesTypeSalesDateSalesAmountSalesChangeDateYear
15108Self7/30/2021 03-01-20212021
15108Self7/30/2021456703-01-20212022
15108Self7/30/2021 03-01-20212023
15108Self7/30/2021 03-01-20212024
15108Self7/30/2021 03-01-20212025

 

When I am trying to join these to tables on below script, I am getting incorrect values for  CustomerID 15108


LOAD
AutoNumber(CustomerID&'-'&Year&'-'&SalesType) as Key,
CustomerID as OLD_CustomerID,
SalesType as OLD_SalesType,
SalesDate as OLD_SalesDate,
SalesAmount as OLD_SalesAmount,
SalesChangeDate as OLD_SalesChangeDate,
Year as OLD_Year
FROM
[C:\Users\S\Desktop\Left Join.xlsx]
(ooxml, embedded labels, table is DataSet1);

Left Join

LOAD AutoNumber(CustomerID&'-'&Year&'-'&SalesType) as Key,
CustomerID as NEW_CustomerID,
SalesType as NEW_SalesType,
SalesDate as NEW_SalesDate,
SalesAmount as NEW_SalesAmount,
SalesChangeDate as NEW_SalesChangeDate,
Year as NEW_Year
FROM
[C:\Users\S\Desktop\Left Join.xlsx]
(ooxml, embedded labels, table is DataSet2);

 

it is duplicating the values but in data i don't have the suitable keys.. rest of values populating good 

Can anyone help me on this to avoid the duplication.

Correct_Answer_0-1624094999250.png

 

Thanks, 

 

 

1 Solution

Accepted Solutions
PadmaPriya
Support
Support

Hello @Correct_Answer 

 

Please follow the below link for data modelling:

Combining tables with Join and Keep ‒ Qlik Sense on Windows

 

Thanks,

Padma Priya

We are just 'like' you and like to be liked when providing a helpful answer. You may press the 'Solution Accepted' button if an answer provided solves your issue.

View solution in original post

1 Reply
PadmaPriya
Support
Support

Hello @Correct_Answer 

 

Please follow the below link for data modelling:

Combining tables with Join and Keep ‒ Qlik Sense on Windows

 

Thanks,

Padma Priya

We are just 'like' you and like to be liked when providing a helpful answer. You may press the 'Solution Accepted' button if an answer provided solves your issue.

View solution in original post