Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
CustomerID | SalesType | SalesDate | SalesAmount | SalesChangeDate | Year |
15108 | Self | 7/30/2021 | 4298 | 03-02-2021 | 2022 |
15108 | Self | 7/30/2021 | 0 | 03-02-2021 | 2022 |
15108 | Self | 7/30/2021 | 4312 | 03-02-2021 | 2022 |
15108 | Self | 7/30/2021 | 0 | 03-02-2021 | 2022 |
15108 | Self | 7/30/2021 | 4298.1 | 03-02-2021 | 2022 |
and
DataSet2:
//03-/01-2021
CustomerID | SalesType | SalesDate | SalesAmount | SalesChangeDate | Year |
15108 | Self | 7/30/2021 | 03-01-2021 | 2021 | |
15108 | Self | 7/30/2021 | 4567 | 03-01-2021 | 2022 |
15108 | Self | 7/30/2021 | 03-01-2021 | 2023 | |
15108 | Self | 7/30/2021 | 03-01-2021 | 2024 | |
15108 | Self | 7/30/2021 | 03-01-2021 | 2025 |
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.
Thanks,
Hello @Correct_Answer
Please follow the below link for data modelling:
Combining tables with Join and Keep ‒ Qlik Sense on Windows
Thanks,
Padma Priya
Hello @Correct_Answer
Please follow the below link for data modelling:
Combining tables with Join and Keep ‒ Qlik Sense on Windows
Thanks,
Padma Priya