Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hei everybody,
i am new at qlik sense and i need to do something like a sub/inner load (maybe with a join or a for each but i dont know whats the best way and how to do it).
That is the current Situation:
I want to create a table with the state of every customer for every day (not just today).
Therefore I need to calculate the state for everyone an every day. If a customer place an order within the last 12 months he/she is active, else inactive
I have 3 tables: Customer, Order and Date
Customer:
Customer |
A |
B |
Order:
ID | Date | Customer |
11 | 1.1.2019 | A |
22 | 1.1.2019 | B |
33 | 2.1.2019 | A |
Date:
Date |
1.1.2019 |
2.1.2019 |
3.1.2019 |
1.1.2020 |
2.1.2020 |
3.1.2020 |
One problem is, that not everybody place an order on everyday but i need the customerstate for every day und every customer. To solve this, I do a cross join on Customer and Date to get the following table:
table1:
Date | Customer |
1.1.2019 | A |
1.1.2019 | B |
2.1.2019 | A |
2.1.2019 | B |
3.1.2019 | A |
3.1.2019 | B |
1.1.2020 | A |
1.1.2020 | B |
2.1.2020 | A |
2.1.2020 | B |
3.1.2020 | A |
3.1.2020 | B |
Now I need to calculate the state for every row. I thought about 2 ways:
Join: I can do a join between the tables 'order' and 'table1' but then I have many missing values and I dont know how to calculate the state of them (how to check if there is an order within the last 12 months).
tableJoin:
Date | Customer | Order |
1.1.2019 | A | 11 |
1.1.2019 | B | 22 |
2.1.2019 | A | 33 |
2.1.2019 | B | |
3.1.2019 | A | |
3.1.2019 | B | |
1.1.2020 | A | |
1.1.2020 | B | |
2.1.2020 | A | |
2.1.2020 | B | |
3.1.2020 | A | |
3.1.2020 | B |
For: something like "for each row" but i have Millions of rows.... So there might be a problem with the calculation time.
The final result should look like:
tablefinal :
Date | Customer | State |
1.1.2019 | A | active |
1.1.2019 | B | active |
2.1.2019 | A | active |
2.1.2019 | B | active |
3.1.2019 | A | active |
3.1.2019 | B | active |
1.1.2020 | A | active |
1.1.2020 | B | active |
2.1.2020 | A | active |
2.1.2020 | B | inactive |
3.1.2020 | A | inactive |
3.1.2020 | B | inactive |
I hope the description is understandable.
Greets,
kotorbe
can you indicate :
the input table and the expected output ?
the initial input
Input tables:
Date
(all dates between 01.01.2017 and today are included)
(just a few dates are shown below)
Date:
LOAD * Inline [
Date
'01.01.2020'
'02.01.2020'
'03.01.2020'
'04.01.2020'
'05.01.2020'
'06.01.2020'
'07.01.2020'
'08.01.2020'
];
Order
(all orders with orderdate and customerID since 2017 are included)
(just a few orders are shown below)
Order:
LOAD * Inline [
Date ,OrderID ,Customer
'01.01.2019' ,11 ,A
'02.01.2019' ,22 ,B
];
Expected Output:
Date Customer State
01.01.2020 A active
01.01.2020 B active
02.01.2020 A inactive
02.01.2020 B active
03.01.2020 A inactive
03.01.2020 B inactive
active: at least 1 Order within the last 12 months (current day included)
inactive: all other