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