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

[#2]

But Still Error in Expression:')' Expected

[Marcio_Campestrini]

Hi Heather

It seems your problem is with the $(vMAX) variable. What's its content?

Márcio

[#4]

LET vMAX  = max(TD_DOC_DT);

If Required then Change to Month as Maximum , then it is useful for me...

regards,

Heather

[Marcio_Campestrini]

Hi Heather

If you look at your image, you can see no value where $(vMAX) should exist. I think that is your problem.

Márcio

[its_anandrjs]

Once Check your load script may be some open and close bracket "( ) " problem.

Regards

Anand

[#7]

Hi,

where you are setting the Variable value. can you post the entire script?

[its_anandrjs]

Did you put the terminator semi colon also like (;) at the end of script.

Anand

[#9]

Every thing is Fine, but it is showing : Error in Expression  : ')'

[#10]

the error is because the varaible value is blank.

you can post the entire script to check why the variable value is blank.

[#11]

LET vMAX  = max(date(TD_DOC_DT));

CN_Customer_Code_Temp:

LOAD

TD_TH_SYS_ID,

TD_DOC_DT,

TD_DOC_DRCR_FLAG,

TD_DOC_AMT,

TD_MAIN_ACNT_CODE,

TD_SUB_ACNT_CODE as [Customer No]

Resident PRV_TRANS_DETAIL_Temp

WHERE TD_MAIN_ACNT_CODE='106100'

and TD_SUB_ACNT_CODE <> NULL();

Concatenate

LOAD

TD_TH_SYS_ID,

TD_DOC_DT,

TD_DOC_DRCR_FLAG,

TD_DOC_AMT,

TD_MAIN_ACNT_CODE,

TD_SUB_ACNT_CODE AS [Customer No]

Resident CUR_TRANS_DETAIL_Temp

WHERE TD_MAIN_ACNT_CODE='106100'

and TD_SUB_ACNT_CODE <> NULL();

CUR_PRV_TRANS:

load

TD_TH_SYS_ID,

TD_DOC_DT,

month(TD_DOC_DT) as TD_DOC_DT_Month,

year(TD_DOC_DT) as TD_DOC_DT_Year,

date(Date#(year(TD_DOC_DT)&'-'&month(TD_DOC_DT),'YYYY-MMM'),'MMM-YYYY') as TD_DOC_DTYearMonth,

TD_DOC_DRCR_FLAG,

TD_DOC_AMT,

//if(TD_DOC_DRCR_FLAG='D',TD_DOC_AMT,0) as Deb_Doc_Amt,

//if(TD_DOC_DRCR_FLAG='C',TD_DOC_AMT,0) as Cred_Doc_Amt,

TD_MAIN_ACNT_CODE,

[Customer No]

//if(Previous([Customer No])=[Customer No],

//numsum( TD_DOC_AMT, peek( 'Bsum' ) ),TD_DOC_AMT) as Bsum

Resident CN_Customer_Code_Temp;

Outer Join(CUR_PRV_TRANS)

Outstanding:

LOAD COMP_CODE,

     ACNT_YEAR,

     CAL_YEAR,

     MAIN_ACNT_CODE,

     SUB_ACNT_CODE as [Customer No],

     SUB_ACNT_CODE as Custcode,

     OPENING,

     DR_CR

FROM

(biff, embedded labels, table is [Sheet 1$]);

drop tables PRV_TRANS_DETAIL_Temp,CUR_TRANS_DETAIL_Temp,CN_Customer_Code_Temp;

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 <= $(vMaxDT) and TD_DOC_DRCR_FLAG = 'D', TD_DOC_AMT ) ) -

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

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

sum( If( TD_DOC_DT <= $(vMaxDT) 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_MAIN_ACNT_CODE;

DROP Table CUR_PRV_TRANS;