IF Statement with Match + AND operator in Load Statement

[Anonymous]

Qlik Community,

I keep getting an 'invalid expression' error specific to the IF statement in my LOAD statement. When I use the same IF statement in a straight table expression it works so I'm wondering there are different syntax requirements for a LOAD statement..

My IF statement is as follows:

,IF(Match(CostCenters,'CC1','CC2') AND Max([ApptHR])-Min([ApptHR]) > 8

  ,1.5

  ,IF(Max([ApptHR])-Min([ApptHR]) > 4

   ,1

   ,0.5))

as ProviderDay

The intent is that only providers in the cost centers CC1 and CC2  who worked more than 8 hours are eligible for a 1.5 day credit, everyone else above 4 hours receives 1 day and below that 0.5 day.

Any insight will be greatly appreciated,

B

[sunny_talwar]

You are using Max() in your load statement, do you accompany it with a Group By Statement?

[its_anandrjs]

Can you provide your full script?

[Anonymous]

Yes, I am including a Group By. I am responding below to Anand with the full load script..

[Anonymous]

LOAD

AppKey

,ContactDT as ProvDate

,Min([ApptHR]) as ProvMinApptHr

,Max([ApptHR]) as ProvMaxApptHr

,Max([ApptHR]) - Min([ApptHR]) as ProvTotalApptHr

,IF(Match(CostCenters,'CC1','CC2') AND Max([ApptHR])-Min([ApptHR]) > 8

  ,1.5

  ,IF(Max([ApptHR])-Min([ApptHR]) > 4

   ,1

   ,0.5))

as ProviderDay

FROM $(CompOvSummaryQVD) (qvd)

Group By

AppKey

,ContactDT

;

[sunny_talwar]

May be this

If(Match(CostCenters,'CC1','CC2')

     If(Max([ApptHR])-Min([ApptHR]) > 8, 1.5,

     If(Max([ApptHR])-Min([ApptHR]) > 4, 1, 0.5))) as ProviderDay

[Anonymous]

Script error still generating with message:

Error in expression: ')' expected

[sunny_talwar]

I am not seeing any issue... this isn't working?

LOAD

AppKey

,ContactDT as ProvDate

,Min([ApptHR]) as ProvMinApptHr

,Max([ApptHR]) as ProvMaxApptHr

,Max([ApptHR]) - Min([ApptHR]) as ProvTotalApptHr

,If(Match(CostCenters,'CC1','CC2')

     If(Max([ApptHR])-Min([ApptHR]) > 8, 1.5,

     If(Max([ApptHR])-Min([ApptHR]) > 4, 1, 0.5))) as ProviderDay

FROM $(CompOvSummaryQVD) (qvd)

Group By AppKey, ContactDT;

[Anonymous]

Not working yet.. still getting the ')' expected script error. I'm not sure if this helps but before including the CostCenter condition, I had the following IF statement in the same LOAD statement and it does run successfully...

,IF(Max([ApptHR])-Min([ApptHR])>8,1.5,IF(Max([ApptHR])-Min([ApptHR])>4,1,0.5)) as ProviderDay

[its_anandrjs · Accepted solution]

Try this also

LOAD

AppKey,

ContactDT as ProvDate,

Min([ApptHR]) as ProvMinApptHr,

Max([ApptHR]) as ProvMaxApptHr,

Max([ApptHR]) - Min([ApptHR]) as ProvTotalApptHr,

IF(  Match(CostCenters,'CC1','CC2') AND

      (Max([ApptHR]) - Min([ApptHR])) > 8,1.5,

IF(  (Max([ApptHR]) - Min([ApptHR])) > 4,1,0.5)) as ProviderDay

FROM $(CompOvSummaryQVD) (qvd)

Group By

AppKey ,ContactDT, CostCenters;