topic If Else statement - Resource hungry? in QlikView

If Else statement - Resource hungry?

helen_pip — Wed, 28 Jun 2017 15:41:51 GMT

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_Pass_Target_TRSP),

$(vColour_Fail_Target_TRSP)

)

Any help would be greatly appreciated

Kind Regards

Helen

Re: If Else statement - Resource hungry?

vishsaggi — Wed, 28 Jun 2017 16:01:39 GMT

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

)))

Re: If Else statement - Resource hungry?

Kushal_Chawda — Wed, 28 Jun 2017 17:13:26 GMT

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