Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear Qlikview user
I am in the process of changing my nested if statements to Pick(match expressions in a bid to improve performance...So i feel as though I have nested if's covered
However, I am looking to improve some of my Else If statements and was wondering if there is a way of using Pick(Match in a Else If statement?
I have had an attempt at it, but it is not quite working
Original statement (Is working)
=If(TB_KPI_ED = 'A' OR TB_KPI_ED = 'B OR TB_KPI_ED = 'C' OR TB_KPI_ED = 'D' or TB_KPI_ED = 'E
', $(vColour_White),
If(TB_KPI_ED = 'F' and Num((Count({<data_source = {'Main'}>}ED_Total_Wait) -Sum({<data_source = {'Main'}, Over4Hrs_Flag={1}>} Over4Hrs_Flag) ) / Count({<data_source = {'Main'}>}Total),'#,##.#%')
>0.95,
$(vColour_Pass_Target_TRSP),
$(vColour_Fail_Target_TRSP)
)))))))
Trying to Use Pick(Match (Is not working)
Pick(Match(TB_KPI_ED,
'A',
'B',
'C',
'D',
'E',
'F' and Num((Count({<data_source = {'Main'}>}ED_Total_Wait) -Sum({<data_source = {'Main'}, Over4Hrs_Flag={1}>} Over4Hrs_Flag) ) / Count({<data_source = {'Main'}>}Total),'#,##.#%')>0.95,
$(vColour_White),
$(vColour_White),
$(vColour_White),
$(vColour_White),
$(vColour_White),
$(vColour_Pass_Target_TRSP),
$(vColour_Fail_Target_TRSP)
)
Any help would be greatly appreciated
Kind Regards
Helen
I doubt if that is possible inside the pick. Never tried. But may be this would help try it? Sorry if i have missed any brackets please cross check.
LET vCount = Num((Count({<data_source = {'Main'}>}ED_Total_Wait) -Sum({<data_source = {'Main'}, Over4Hrs_Flag={1}>} Over4Hrs_Flag))
/ Count({<data_source = {'Main'}>}Total),'#,##.#%');
IF(TB_KPI_ED = 'F' and $(vCount) > 0.95, $(vColour_Pass_Target_TRSP),
IF(Not Match(TB_KPI_ED, 'A', 'B', 'C', 'D', 'E', 'F'), $(vColour_Fail_Target_TRSP),
Pick(Match(TB_KPI_ED, 'A', 'B', 'C', 'D', 'E'), $(vColour_White), $(vColour_White), $(vColour_White), $(vColour_White), $(vColour_White))
)))
LOAD *,
If(match(TB_KPI_ED , 'A' , 'B ,'C', 'D' , 'E'),1,if(TB_KPI_ED='F',2,3)) as Flag
FROM table;
Pick(match(Flag,'1','2','3'),
$(vColour_White),
if(Num((Count({<data_source = {'Main'}>}ED_Total_Wait) -Sum({<data_source = {'Main'}, Over4Hrs_Flag={1}>} Over4Hrs_Flag) ) / Count({<data_source = {'Main'}>}Total),'#,##.#%')>0.95, $(vColour_Pass_Target_TRSP)),
$(vColour_Fail_Target_TRSP))