Case statement..

[Anonymous]

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.

stalwar1‌youssefbelloum‌

[undergrinder]

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]

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]

hi,

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

G.

[Anonymous]

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.

[Anil_Babu_Samineni]

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

[Anonymous]

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

[Anonymous]

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]

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

[Anonymous]

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