Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
All fields without an aggregration should be included in the group by clause
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
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,
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
Error in Expression :')' expected ...
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
Error in Expression :')' expected ...
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