Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have been trying to figure out a way to calculate the out of a column depending on the values of other columns.
From the table below I would like to create an expression to say:
case if Level = 'AOG' and DateTime is between 18:00 and 08:00 (following morning) then 'AOG OO'
or if Level = 'AOG' and DateTime is on a Weekend then 'AOG OO' Else Level
Customer | Level | DateTime |
---|---|---|
A | AOG | 2018-05-01 09:08:06 |
B | Routine | 2018-05-03 13:23:04 |
B | Routine | 2018-05-07 19:11:07 |
A | AOG | 2018-05-11 23:30:02 |
OUTPUT REQUIRED:
Customer | OriginalLevel | NewLevel | DateTime |
---|---|---|---|
A | AOG | AOG | 2018-05-01 09:08:06 |
B | Routine | Routine | 2018-05-03 13:23:04 |
B | Routine | Routine | 2018-05-07 19:11:07 |
A | AOG | AOG OO | 2018-05-11 23:30:02 |
This calculation is being done to work out any levels that fall into out of hours (out of normal working hours).
Thank you in advance for your assistance.
Thanks all, I managed to get it working by doing the following:
=IF(PriorityLevel = 'AOG' and (hour(OutTimestamp)>18 and hour(OutTimestamp)<24) Or (AMPM = 'AM' and hour(OutTimestamp)<8) Or (weekday(OutTimestamp)='Sat' or weekday(OutTimestamp)='Sun'),'AOG OOH',PriorityLevel)
Hi Raman,
you can implement this logic in load script, and in chart expression as well.
However there is no case-when statement in Qlik, you can use nested if for that.
Simple: If(condition,true,false)
Nested: If(condition,if(condition,true,false),false)
G.
Hi,
there is a CASE logic on Qlik: https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/ScriptControlS...
but he don't need it to create his new field, an IF statement is sufficient
hi,
you're right, but it is rather swith-case control statement like in C, C#, than case-when logic like in SQL.
G.
Hi Youssef,
I have gone over the information in the link but am having trouble understanding script. Not really sure how to put this function in practice for what I require. I have no issue writing this up in SQL but not much experience with Qlik scripts.
Switch I
Case 1
LOAD '$(I): CASE 1' as case autogenerate 1;
Case 2
LOAD '$(I): CASE 2' as case autogenerate 1;
Default
LOAD '$(I): DEFAULT' as case autogenerate 1;
End Switch
Above is the example they had in the link.
Create this field in script? Where Time comes from
Time(Date#(DateTime,'YYYY-MM-DD hh:mm:ss'),'hh:mm') as Time
Then
If(Level='AOG' and (Time>='18:00' and Time<='08:00'),'AOG OO',OriginalLevel) as NewLevel
Hi Anil,
Thanks for the help.
I added the first part to the load editor and the second as an expression but it just returns 'AOG' not 'AOG OOH'. It should be returning 5 'AOG OOH' and 5 'AOG'.
I have a look at the time function and it seems to be returning blank values.
Time(Date#(DateTime,'YYYY-MM-DD hh:mm:ss'),'hh:mm') as Time
Possibly is something missing from above?
I've added the link for reference for Gabor, you don't need a CASE SWITCH.. you only need an IF statement, try Anil suggestion
I tried Anil's but was getting blank rows for time so I tweaked this to below and this now returns time:
Timestamp(OutTimestamp,'hh:mm') as TimeOnly
The expression I am using is:
=If(PriorityLevel = 'AOG' and (OutTimeOnly >='18:00' and OutTimeOnly <='08:00'),'AOG OOH',PriorityLevel)
This still only returns AOG even though I have rows which should fall into 'AOG OOH'.