Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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

1 Solution

Accepted Solutions
its_anandrjs

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;

View solution in original post

10 Replies
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
Not applicable
Author

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

Anonymous
Not applicable
Author

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
Not applicable
Author

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 applicable
Author

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

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;