topic Load if isnull in Load Script (sub/inner load) in App Development

Load if isnull in Load Script (sub/inner load)

kotorbe — Wed, 09 Jun 2021 07:03:39 GMT

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

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

Re: Load if isnull in Load Script (sub/inner load)

Taoufiq_Zarra — Wed, 09 Jun 2021 08:30:41 GMT

@kotorbe

can you indicate :

the input table and the expected output ?

the initial input

Re: Load if isnull in Load Script (sub/inner load)

kotorbe — Wed, 09 Jun 2021 09:55:38 GMT

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