Error in the SCript ?

[#1]

Outstanding:

Load

DR_CR,

TD_DOC_DRCR_FLAG,

TD_DOC_DT,

[Customer No],

TD_DOC_DT_Year,

TD_DOC_DT_Month,

TD_MAIN_ACNT_CODE,

TD_DOC_DTYearMonth,

if(DR_CR='D',

sum(OPENING) +

sum( If( TD_DOC_DT <= $(vMAX) and TD_DOC_DRCR_FLAG = 'D', TD_DOC_AMT ) ) -

sum( If( TD_DOC_DT <= $(vMAX) and TD_DOC_DRCR_FLAG = 'C', TD_DOC_AMT ) ),

sum( If( TD_DOC_DT <= $(vMAX) and TD_DOC_DRCR_FLAG = 'D', TD_DOC_AMT) )-

sum( If( TD_DOC_DT <= $(vMAX) and TD_DOC_DRCR_FLAG = 'C', TD_DOC_AMT) )-

sum(OPENING))  as SumField

Resident CUR_PRV_TRANS

Group BY [Customer No],TD_DOC_DT_Month,TD_DOC_DT,TD_MAIN_ACNT_CODE;

regards,

Heather

[its_anandrjs]

When use Group by you have to use all dimension which is available in the load script

Outstanding:

Load

DR_CR,

TD_DOC_DRCR_FLAG,

TD_DOC_DT,

[Customer No],

TD_DOC_DT_Year,

TD_DOC_DT_Month,

TD_MAIN_ACNT_CODE,

TD_DOC_DTYearMonth,

if(DR_CR='D',

sum(OPENING) +

sum( If( TD_DOC_DT <= $(vMAX) and TD_DOC_DRCR_FLAG = 'D', TD_DOC_AMT ) ) -

sum( If( TD_DOC_DT <= $(vMAX) and TD_DOC_DRCR_FLAG = 'C', TD_DOC_AMT ) ),

sum( If( TD_DOC_DT <= $(vMAX) and TD_DOC_DRCR_FLAG = 'D', TD_DOC_AMT) )-

sum( If( TD_DOC_DT <= $(vMAX) and TD_DOC_DRCR_FLAG = 'C', TD_DOC_AMT) )-

sum(OPENING))  as SumField

Resident CUR_PRV_TRANS

Group BY

DR_CR,

TD_DOC_DRCR_FLAG,

TD_DOC_DT,

[Customer No],

TD_DOC_DT_Year,

TD_DOC_DT_Month,

TD_MAIN_ACNT_CODE,

TD_DOC_DTYearMonth;

Regards

Anand

[stigchel]

All fields without an aggregration should be included in the group by clause

[alexandros17]

try with :

Outstanding:

Load

[Customer No],

TD_DOC_DT_Month,

TD_DOC_DT,

TD_MAIN_ACNT_CODE,

if(DR_CR='D',

sum(OPENING) +

sum( If( TD_DOC_DT <= $(vMAX) and TD_DOC_DRCR_FLAG = 'D', TD_DOC_AMT ) ) -

sum( If( TD_DOC_DT <= $(vMAX) and TD_DOC_DRCR_FLAG = 'C', TD_DOC_AMT ) ),

sum( If( TD_DOC_DT <= $(vMAX) and TD_DOC_DRCR_FLAG = 'D', TD_DOC_AMT) )-

sum( If( TD_DOC_DT <= $(vMAX) and TD_DOC_DRCR_FLAG = 'C', TD_DOC_AMT) )-

sum(OPENING))  as SumField

Resident CUR_PRV_TRANS

Group BY [Customer No],TD_DOC_DT_Month,TD_DOC_DT,TD_MAIN_ACNT_CODE;

let me know if it works

[PrashantSangle]

Hi,

You are missing TD_DOC_DT_Year,TD_DOC_DTYearMonth field from group by.

If you dont want to include it in group by then use

ONLY(TD_DOC_DT_Year) as TD_DOC_DT_Year,

in your script.

When you are using aggreation function like sum() then you have to include all other field in group by clause.

If you want to avoid it to using in group by clause then use ONLY() as i have suggested.

Your expression can be more optimized if you want.

Regards,

[its_anandrjs]

If you want to group by by only four fields that is [Customer No],TD_DOC_DT_Month,TD_DOC_DT,TD_MAIN_ACNT_CODE

then use only this four fields in the load script otherwise you have to use all fields in group by statement

Regards

Anand

[#7]

Error in Expression :')' expected ...

[alexandros17]

The logic I want to suggest is that besides

[Customer No],

TD_DOC_DT_Month,

TD_DOC_DT,

TD_MAIN_ACNT_CODE,

use the field computed with sum

because only fields in group by can be extracted from a load instruction

[#9]

Error in Expression :')' expected ...

Error in the SCript ?

[its_anandrjs]

Hi,

Let me know how many fields are to be loaded from the load script if only four requires then comment the rest of the fields because if you use this fields all to be need for the group by statement see the load script

Outstanding:

Load

//DR_CR,

//TD_DOC_DRCR_FLAG,

TD_DOC_DT,

[Customer No],

//TD_DOC_DT_Year,

TD_DOC_DT_Month,

TD_MAIN_ACNT_CODE,

//TD_DOC_DTYearMonth,

if(DR_CR='D',

sum(OPENING) +

sum( If( TD_DOC_DT <= $(vMAX) and TD_DOC_DRCR_FLAG = 'D', TD_DOC_AMT ) ) -

sum( If( TD_DOC_DT <= $(vMAX) and TD_DOC_DRCR_FLAG = 'C', TD_DOC_AMT ) ),

sum( If( TD_DOC_DT <= $(vMAX) and TD_DOC_DRCR_FLAG = 'D', TD_DOC_AMT) )-

sum( If( TD_DOC_DT <= $(vMAX) and TD_DOC_DRCR_FLAG = 'C', TD_DOC_AMT) )-

sum(OPENING))  as SumField

Resident CUR_PRV_TRANS

Group BY [Customer No],TD_DOC_DT_Month,TD_DOC_DT,TD_MAIN_ACNT_CODE;

In bold is the script and line start with // is commented that is not loaded.

Regards

Anand