Qlik Community

Ask a Question

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Join us March 10th, 7 ways modern analytics can help you take smarter action. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Zenpark_BI
Contributor II
Contributor II

Script - Label field without nested if statements

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 :

IdCreatedBeginEnd
A01/01/2021 10:00:0003/01/2021 12:00:0003/01/2021 14:00:00
B02/01/2021 14:00:0016/04/2021 08:00:0018/04/2021 18:00:00

 

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

IdDateLeadTime_DayNumLeadTime_DayName_EN
A01/01/20212D-2
A02/01/20211D-1
A03/01/20210D-0
B02/01/2021104D-104
B03/02/2021103D-103
B.........
B15/04/20211D-1
B16/04/20210D-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
];

 

2 Solutions

Accepted Solutions
Nicole-Smith

Zenpark_BI
Contributor II
Contributor II

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

Zenpark_BI_0-1614075211330.png

 

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

View solution in original post

4 Replies
Nicole-Smith

Zenpark_BI
Contributor II
Contributor II

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

Zenpark_BI_0-1614075211330.png

 

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

View solution in original post

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
Contributor II
Contributor II

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

Thank you for your help.