Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
My script in qlikview gives an error as "Invalid Expression" while reloading and fails.
I am not sure about this error.
Any idea why qlikview throws an error like this and is there any solution for this?
sum(Total_resource)
this syntax only worked with group by .
so this gives the error in your expression
I am using Sum, the code goes like this:
Temp2:
Load
[Key_ID],
(sum(Total_resource) * 13 * 7/
//PHASE 1
If([Cat1] = 'PHASE 1',
If([Cat2] = 'Phase 1',
If([E_Num]=[P_Id] and [E_Type]='Phase Review Decision - Cancelled',
If(IsNull([St_Dt]), [E_Dt] - [PC_Dt], [E_Dt] - [St_Dt]),
If(IsNull(MAXSTRING([L_Dt])),
If(IsNull([St_Dt]), Today() - [PC_Dt], Today() - [St_Dt])
,
If(IsNull([St_Dt]), MAXSTRING([L_Dt]) - [PC_Dt], MAXSTRING([L_Dt]) - [St_Dt]))),
If([Actual_dur_1] >=1, [Actual_dur_1], 0))
)
)as [source]
Resident Temp1;
and it goes on for n number of phases, I have separated to check what's wrong.
I tried using Group By, but was not able to make this work.
Appreciate your help.
sum(Total_resource)
this syntax only worked with group by .
so this gives the error in your expression
Thanks vishwaranjan.
I already tried using group by but it didn't work.
Load
[Key_ID],
sum(Total_resource) as [source]
Resident Temp1 Group by [Key_Load];
Just try to load this
It is reload without error then error is in if statement
else it is reload with error then Total_resource might be text field
Regards,
Kabilan K/
try this,
Temp2:
Load
[Key_ID],
(sum(Total_resource) * 13 * 7/
//PHASE 1
Sum(If([Cat1] = 'PHASE 1',
If([Cat2] = 'Phase 1',
If([E_Num]=[P_Id] and [E_Type]='Phase Review Decision - Cancelled',
If(IsNull([St_Dt]), [E_Dt] - [PC_Dt], [E_Dt] - [St_Dt]),
If(IsNull(MAXSTRING([L_Dt])),
If(IsNull([St_Dt]), Today() - [PC_Dt], Today() - [St_Dt])
,
If(IsNull([St_Dt]), MAXSTRING([L_Dt]) - [PC_Dt], MAXSTRING([L_Dt]) - [St_Dt]))),
If([Actual_dur_1] >=1, [Actual_dur_1], 0))
) )
)as [source]
Resident Temp1;
Thanks for the response Kabilan.
Total_resource is a numeric field.
I tried the second option also, but it gives an error as :
Error: Error in expression:
Nested aggregation not allowed
If u r using Sum(Total_resource), then do group by...otherwise try below..
Temp2:
Load
[Key_ID],
Total_resource * 13 * 7/
//PHASE 1
If([Cat1] = 'PHASE 1',
If([Cat2] = 'Phase 1',
If([E_Num]=[P_Id] and [E_Type]='Phase Review Decision - Cancelled',
If(IsNull([St_Dt]), [E_Dt] - [PC_Dt], [E_Dt] - [St_Dt]),
If(IsNull(MAXSTRING([L_Dt])),
If(IsNull([St_Dt]), Today() - [PC_Dt], Today() - [St_Dt])
,
If(IsNull([St_Dt]), MAXSTRING([L_Dt]) - [PC_Dt], MAXSTRING([L_Dt]) - [St_Dt]))),
If([Actual_dur_1] >=1, [Actual_dur_1], 0))
)
as [source]
Resident Temp1;