topic Re: script error in QlikView

script error

qvqfqlik — Wed, 20 Jul 2016 20:36:54 GMT

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

please help.

Re: script error

sunny_talwar — Wed, 20 Jul 2016 20:39:36 GMT

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.

Re: script error

sunny_talwar — Wed, 20 Jul 2016 20:40:15 GMT

May be you just need this:

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

;

Re: script error

qvqfqlik — Wed, 20 Jul 2016 20:53:23 GMT

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

Re: script error

vishsaggi — Wed, 20 Jul 2016 21:45:28 GMT

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 * ?

Re: script error

qvqfqlik — Wed, 20 Jul 2016 21:47:32 GMT

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

Re: script error

vikramv — Wed, 20 Jul 2016 21:52:51 GMT

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;

Re: script error

vikramv — Wed, 20 Jul 2016 22:03:36 GMT

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 ?

Re: script error

qvqfqlik — Wed, 20 Jul 2016 22:52:05 GMT

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 ?

Re: script error

vikramv — Wed, 20 Jul 2016 23:40:44 GMT

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.

Re: script error

qvqfqlik — Thu, 21 Jul 2016 00:03:41 GMT

this is correct too. Thank you

Re: script error

sunny_talwar — Thu, 21 Jul 2016 01:01:37 GMT

Is everything resolved now?

Re: script error

jayanttibhe — Thu, 21 Jul 2016 01:29:26 GMT

Hi Sunny,

I know this has been resolved but in this case preceding load is not required.

Like this :

;

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

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

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

Resident

PSI_Data

Group by

%MATERIAL_KEY,

%LOCATION_KEY,

%ACCT_CAL_FACT_KEY

;

Re: script error

qvqfqlik — Thu, 21 Jul 2016 03:20:00 GMT

yes, thankyou

Re: script error

vikramv — Thu, 21 Jul 2016 05:57:28 GMT

Glad you found it!!