Qlik Community

Qlik Healthcare User Group

Highlighted
bsmorri1
New Contributor

IF Statement with Match + AND operator in Load Statement

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

Tags (1)
1 Solution

Accepted Solutions

Re: IF Statement with Match + AND operator in Load Statement

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;

10 Replies
MVP
MVP

Re: IF Statement with Match + AND operator in Load Statement

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

Re: IF Statement with Match + AND operator in Load Statement

Can you provide your full script?

bsmorri1
New Contributor

Re: IF Statement with Match + AND operator in Load Statement

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

bsmorri1
New Contributor

Re: IF Statement with Match + AND operator in Load Statement

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

;

MVP
MVP

Re: IF Statement with Match + AND operator in Load Statement

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

bsmorri1
New Contributor

Re: IF Statement with Match + AND operator in Load Statement

Script error still generating with message:

Error in expression: ')' expected

MVP
MVP

Re: IF Statement with Match + AND operator in Load Statement

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;

bsmorri1
New Contributor

Re: IF Statement with Match + AND operator in Load Statement

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

Re: IF Statement with Match + AND operator in Load Statement

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;