Script - Label field without nested if statements

Zenpark_BI — Mon, 22 Feb 2021 18:54:26 GMT

Hi,

Working in the data load editor, I would like to label a field with informations from a Load * Inline table.

The trick is, I don't have a key field to apply the joint.

Here is the situation :

I want to generate a pick up (montée en charge) chart for the Revenue Management team.

I have a list of booking with the following informations :

Id	Created	Begin	End
A	01/01/2021 10:00:00	03/01/2021 12:00:00	03/01/2021 14:00:00
B	02/01/2021 14:00:00	16/04/2021 08:00:00	18/04/2021 18:00:00

I've generate the dates between the Created and the Begin to have a table (BookingWithDate) like that :

Id	Date	LeadTime_DayNum	LeadTime_DayName_EN
A	01/01/2021	2	D-2
A	02/01/2021	1	D-1
A	03/01/2021	0	D-0
B	02/01/2021	104	D-104
B	03/02/2021	103	D-103
B	...	...	...
B	15/04/2021	1	D-1
B	16/04/2021	0	D-0

It works to create a line chart with :

- Dimension 1 : LeadTime_DayNum

- Dimension 2 : date(floor(Begin),'DD/MM/YYYY')

- Measure : count(distinct Id)

However, as we open booking up to 6 month prior, the chart has too many data in the LeadTime_DayNum to be usable, so the idea is to gather LeadTime_Num together to view them as Month.

I'm not keen on the ide to use nested if statement like so :

left join (BookingWithDate) Id, Date, if(LeadTime_DayNum<=186 and LeadTime_DayNum>155,dual('M-6',6) , if(LeadTime_DayNum<=155 and LeadTime_DayNum>124,dual('M-5',5) , if(LeadTime_DayNum<=124 and LeadTime_DayNum>93,dual('M-4',4) , if(LeadTime_DayNum<=93 and LeadTime_DayNum>62,dual('M-3',3) , if(LeadTime_DayNum<=62 and LeadTime_DayNum>31,dual('M-2',2) , if(LeadTime_DayNum<=31 and LeadTime_DayNum<0, dual('M-1',1) as LeadTime_Month Resident BookingWithDate;

It's not easy to read and to maintain if I wanted to add more complexity like a step every 14 days.

I thought of creating a Load*Inline table with the upper and lower bound and other dimensions like that

_LOADINLINE_LeadTimeMonthInfos: LOAD * INLINE [ MaxDay, MinDayExcluded, LeadTime_MonthNum, LeadTime_MonthName_FR, LeadTime_MonthName_EN 186, 155, 6, M-6, M-6 155, 124, 5, M-5, M-5 124, 93, 4, M-4, M-4 93, 62, 3, M-3, M-3 62, 31, 2, M-2, M-2 31, 0, 1, M-1, M-1 ];

And the idea was to do a left join like so

left join (BookingWithDate) LOAD LeadTime_MonthNum Resident _LOADINLINE_LeadTimeMonthInfos where LeadTime_DayNum <= MaxDay and LeadTime_DayNum>MinDayExcluded ;

It obvisouly don't work as the field LeadTime_DayNum isn't found.

A solution might be :

Generate in a table with the 186 possibilities of LeadTime_DayNum
Do a left join a create a cartesian product
Then keep only the lines where the conditions are true (LeadTime_DayNum <= MaxDay and LeadTime_DayNum>MinDayExcluded)

It should work but it seem unelegant and a hefty solution ... Do you have a better idea ?

Note : I will apply the same logic but this time with time of day but without generating all hours just the four period (Night, Morning, Afternoon, Evening).

_LOADINLINE_SplitTimeOfDay: LOAD * INLINE [ MinHour, MaxHourExcluded, TimeOfDayNum, TimeOfDayName_FR, TimeOfDayName_EN 0, 6, 0, Nuit, Night 6, 12, 1, Matin, Morning 12, 18, 2, Après-midi, Afternoon 18, 22, 3, Soirée, Evening 22, 24, 0, Nuit, Night ];

Re: Script - Label field without nested if statements

Nicole-Smith — Mon, 22 Feb 2021 21:57:18 GMT

The class() function allows you to create buckets that have a consistent step:
https://help.qlik.com/en-US/cloud-services/Subsystems/Hub/Content/Sense_Hub/Scripting/ConditionalFunctions/class.htm

Re: Script - Label field without nested if statements

Zenpark_BI — Tue, 23 Feb 2021 10:13:42 GMT

Thank for the idea !

I twisted it a little to get a single digit number at the end and it worked :

round(SubField(class(LeadTime_DayNum,365/12),'<',1)/(365/12)) as LeadTime_MonthNum

_LOADINLINE_LeadTimeMonthInfos: LOAD * INLINE [ LeadTime_MonthNum, LeadTime_MonthName_FR, LeadTime_MonthName_EN 5, M-5, M-5 4, M-4, M-4 3, M-3, M-3 2, M-2, M-2 1, M-1, M-1 0, M-0, M-0 ];

I generated the following chart

However, it won't work with the second part of my problem as the buckets don't have the same steps : 8 hours for the night, 6 for the morning and afternoon, and 4 for the evening ...

Re: Script - Label field without nested if statements

Nicole-Smith — Tue, 23 Feb 2021 15:14:41 GMT

Unfortunately if your buckets are different sizes, you'll need to use the nested if statements. But I'm glad you got the class() function to work for your other piece!

Re: Script - Label field without nested if statements

Zenpark_BI — Fri, 26 Feb 2021 10:31:48 GMT

At least I've asked the questions, so let's go for the nested if.

Thank you for your help.

topic Re: Script - Label field without nested if statements in App Development

Script - Label field without nested if statements

Re: Script - Label field without nested if statements

Re: Script - Label field without nested if statements

Re: Script - Label field without nested if statements

Re: Script - Label field without nested if statements