Group By ? Invalid Expression Error

[#1]

Hi All,

I am getting group by error ? how to resolve it...

MAin:

load

     MS_MAIN_ACNT_CODE,

  CUST_CODE1,

  OST_SUB_ACNT_CODE,

  OST_FC_ADJ_AMT,

  OST_FC_ORG_AMT,

  Sum(OST_FC_ORG_AMT-OST_FC_ADJ_AMT) as DebtorsOutStandingAmount,

  if(OST_DUE_DT<Today(),sum(OST_FC_ORG_AMT-OST_FC_ADJ_AMT)) as DebtorsOverdueAmount,

  CURR_CODE,

  OST_DOC_DT,

  OST_DUE_DT

Resident FT_OS

Group by CUST_CODE1;

DROP Table FT_OS;

[Anonymous]

You need to group by all fields you're not aggregating:

MAin:

load

     MS_MAIN_ACNT_CODE,

  CUST_CODE1,

  OST_SUB_ACNT_CODE,

  OST_FC_ADJ_AMT,

  OST_FC_ORG_AMT,

  Sum(OST_FC_ORG_AMT-OST_FC_ADJ_AMT) as DebtorsOutStandingAmount,

  if(OST_DUE_DT<Today(),sum(OST_FC_ORG_AMT-OST_FC_ADJ_AMT)) as DebtorsOverdueAmount,

  CURR_CODE,

  OST_DOC_DT,

  OST_DUE_DT

Resident FT_OS

Group by MS_MAIN_ACNT_CODE,CUST_CODE1,OST_SUB_ACNT_CODE,  OST_FC_ADJ_AMT, OST_FC_ORG_AMT,CURR_CODE,  OST_DOC_DT,  OST_DUE_DT

;

DROP Table FT_OS;

[#3]

My debtors Outstanding amt & Debtors over due amount is correct in the script ?

Debtors Outstanding amount :  Difference  of OST_FC_ORG_AMT -  OST_FC_ADJ_AMT

Debtors Overdue amount : IF OST_DUE_DT< Today's Date then Outstanding Amount is the Overdue Amount

Give me expression for this ?

DSO as on month end :  = (last 12 months average outsanding / last 12 months average sales)*365

Regards

Helen

[Anonymous]

You may want to make some adjustments to your expressions to handle nulls:

RangeSum(OST_FC_ORG_AMT)-RangeSum(OST_FC_ADJ_AMT) as DebtorsOutStandingAmount,

  if(OST_DUE_DT<Today(),RangeSum(OST_FC_ORG_AMT)-RangeSum(OST_FC_ADJ_AMT)) as DebtorsOverdueAmount,

Not sure what you mean by

DSO as on month end :  = (last 12 months average outsanding / last 12 months average sales)*365

[#5]

getting Error for above expr ...

Aggregation Expressions required by groupby clause ?

[Anonymous]

Guess it doesn't like the ragesum as an aggregate:

NumSum(OST_FC_ORG_AMT)-NumSum(OST_FC_ADJ_AMT) as DebtorsOutStandingAmount,

  if(OST_DUE_DT<Today(),NumSum(OST_FC_ORG_AMT)-NumSum(OST_FC_ADJ_AMT)) as DebtorsOverdueAmount,

[preminqlik]

hi , since the else expression also should be aggrigated in if expression , try the below solutions and let me know.

MAin:

load

     MS_MAIN_ACNT_CODE,

  CUST_CODE1,

  OST_SUB_ACNT_CODE,

  OST_FC_ADJ_AMT,

  OST_FC_ORG_AMT,

  Sum(OST_FC_ORG_AMT-OST_FC_ADJ_AMT) as DebtorsOutStandingAmount,

  if(OST_DUE_DT<Today(),sum(OST_FC_ORG_AMT-OST_FC_ADJ_AMT),sum(0)) as DebtorsOverdueAmount,

  CURR_CODE,

  OST_DOC_DT,

  OST_DUE_DT

Resident FT_OS

Group by MS_MAIN_ACNT_CODE,CUST_CODE1,OST_SUB_ACNT_CODE,  OST_FC_ADJ_AMT, OST_FC_ORG_AMT,CURR_CODE,  OST_DOC_DT,  OST_DUE_DT

;

DROP Table FT_OS;

or

try this

MAin:

load

     MS_MAIN_ACNT_CODE,

  CUST_CODE1,

  OST_SUB_ACNT_CODE,

  OST_FC_ADJ_AMT,

  OST_FC_ORG_AMT,

  Sum(OST_FC_ORG_AMT-OST_FC_ADJ_AMT) as DebtorsOutStandingAmount,

  sum(if(OST_DUE_DT<Today(),OST_FC_ORG_AMT-OST_FC_ADJ_AMT,0)) as DebtorsOverdueAmount,

  CURR_CODE,

  OST_DOC_DT,

  OST_DUE_DT

Resident FT_OS

Group by MS_MAIN_ACNT_CODE,CUST_CODE1,OST_SUB_ACNT_CODE,  OST_FC_ADJ_AMT, OST_FC_ORG_AMT,CURR_CODE,  OST_DOC_DT,  OST_DUE_DT

;

DROP Table FT_OS;

[#8]

Hi Prem,

I need to get this report as on Every month 1st..

I need to get  Debtor Outstanding Amount as : Sum(OST_FC_ORG_AMT - OST_FC_ADJ_AMT )

& Over Due Amount is : If OST_DUE_DT < Today, Then Debtor Oustanding amount Else Zero

give me proper Scripting...

Regards,

Helen

[preminqlik]

hi betty , shedule this application on every month 1st and store qvds in a specific folder with respective to month wise qvd. and call this qvds in final application to show historical data.

for example like below

store tablename into path\tablename&Date(today(),'MMM-YYYY').qvd

and now you can call created qvds month wise

[simondachstr]

Using NumSum() (Same like RangeSum()) you are aggregating columns rather than rows. So you won't need a group by.