Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

rsingh43
Contributor

Case statement..

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

CustomerLevelDateTime
AAOG2018-05-01 09:08:06
BRoutine2018-05-03 13:23:04
BRoutine2018-05-07 19:11:07
AAOG2018-05-11 23:30:02

OUTPUT REQUIRED:

CustomerOriginalLevelNewLevelDateTime
AAOGAOG2018-05-01 09:08:06
BRoutineRoutine2018-05-03 13:23:04
BRoutineRoutine2018-05-07 19:11:07
AAOGAOG OO2018-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.

stalwar1youssefbelloum

1 Solution

Accepted Solutions
rsingh43
Contributor

Re: Case statement..

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)

13 Replies
undergrinder
Valued Contributor II

Re: Case statement..

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.

YoussefBelloum
Esteemed Contributor

Re: Case statement..

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

undergrinder
Valued Contributor II

Re: Case statement..

hi,

you're right, but it is rather swith-case control statement like in C, C#, than case-when logic like in SQL.

G.

rsingh43
Contributor

Re: Case statement..

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.

Re: Case statement..

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

rsingh43
Contributor

Re: Case statement..

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

rsingh43
Contributor

Re: Case statement..

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?

YoussefBelloum
Esteemed Contributor

Re: Case statement..

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

rsingh43
Contributor

Re: Case statement..

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