Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 :
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
];
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...
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 ...
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...
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 ...
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!
At least I've asked the questions, so let's go for the nested if.
Thank you for your help.