Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
You are using Max() in your load statement, do you accompany it with a Group By Statement?
Can you provide your full script?
Yes, I am including a Group By. I am responding below to Anand with the full load script..
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
;
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
Script error still generating with message:
Error in expression: ')' expected
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;
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
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;