Script - Label field without nested if statements

[Zenpark_BI]

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
];

[Nicole-Smith · Accepted solution]

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/ConditionalFun...

[Zenpark_BI · Accepted solution]

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 ...

[Nicole-Smith]

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!

[Zenpark_BI]

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

Thank you for your help.