script error

[qvqfqlik]

I am getting Invalid expression script error . I might be overlooking, but couldn't find whats wrong with this.

please help.

[sunny_talwar]

I think the error is in the preceding load here:

	Load *,
		Sum(Avg_WKLY_DMD_Current + Avg_WKLY_DMD_2Weeks)/3		as Avg_WKLY_DMD

;

This has aggregation (Sum), but no Group By statement.

[sunny_talwar · Accepted solution]

May be you just need this:

Load *,(Avg_WKLY_DMD_Current + Avg_WKLY_DMD_2Weeks)/3as Avg_WKLY_DMD

;

[qvqfqlik]

If i remove * in my original script its working , but i will get only that calculation and i am not getting other dimensions

[vishsaggi]

That is strange. Can you try not creating Mat_Loc_Key and run again.

And also try to copy paste the field names in preceding load rather than * ?

[qvqfqlik]

I tried that too... It gave me the same error

[vikramv]

May try like this ...

Temp:

LOAD

  %MATERIAL_KEY,

     %LOCATION_KEY,

     %ACCT_CAL_FACT_KEY

  , Sum(Avg_WKLY_DMD_Current) as Avg_WKLY_DMD_Current

  , Sum(Avg_WKLY_DMD_2Weeks) as Avg_WKLY_DMD_2Weeks

  , %MATERIAL_KEY&'-'&%LOCATION_KEY as Mat_Loc_Key

  Resident

  PSI_Data

  Group by

  %MATERIAL_KEY,

     %LOCATION_KEY,

     %ACCT_CAL_FACT_KEY,

    %MATERIAL_KEY&'-'&%LOCATION_KEY

  ;

Final:

NoConcatenate

  Load *,

  Sum(Avg_WKLY_DMD_Current + Avg_WKLY_DMD_2Weeks)/3 as Avg_WKLY_DMD

Resident Temp

Group by

%MATERIAL_KEY,

     %LOCATION_KEY,

     %ACCT_CAL_FACT_KEY,

     Mat_Loc_Key

;

Drop table Temp;

[vikramv]

Its working when remving * ,  because you are using a preceeding load only one field aggregated on the beneeth table.

Please check my updated script where you have to use 2 group by's (In Bold).

Have you used Group By's in both the load statements as I mentioned ?

[qvqfqlik]

yes i used group by and also did as you told...but still same error...

we cannot group by on the key ? as its not in resident table ?

[vikramv]

I am not sure what key are you referring...but debug something like

-- > remove Sum from the Final table and try to reload in debug mode without group by then if you want to do some aggregation you can the use another resident load on the Final and then try adding sum like below 

Sum(Avg_WKLY_DMD_Current + Sum(Avg_WKLY_DMD_2Weeks))/3 with group by on the fields you want to aggregate.

.