
Re: Invalid Expression Error
S P Jul 30, 2013 8:09 AM (in response to S P)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.

Re: Invalid Expression Error
Vishwaranjan Kumar Jul 30, 2013 8:11 AM (in response to S P)sum(Total_resource)
this syntax only worked with group by .
so this gives the error in your expression

Re: Invalid Expression Error
S P Jul 30, 2013 8:18 AM (in response to Vishwaranjan Kumar)Thanks vishwaranjan.
I already tried using group by but it didn't work.


Re: Invalid Expression Error
Kabilan Kumarasamy Jul 30, 2013 8:18 AM (in response to S P)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/

Re: Invalid Expression Error
Kabilan Kumarasamy Jul 30, 2013 8:22 AM (in response to Kabilan Kumarasamy)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;

Re: Invalid Expression Error
S P Jul 31, 2013 2:42 AM (in response to Kabilan Kumarasamy)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

Re: Invalid Expression Error
Bikash S Jul 31, 2013 3:05 AM (in response to S P)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;



