topic Re: Group By ? Invalid Expression Error in QlikView

Group By ? Invalid Expression Error

Thu, 16 Oct 2014 17:22:49 GMT

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;

Re: Group By ? Invalid Expression Error

Anonymous — Thu, 16 Oct 2014 17:25:35 GMT

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;

Re: Group By ? Invalid Expression Error

Thu, 16 Oct 2014 17:46:48 GMT

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

Re: Group By ? Invalid Expression Error

Anonymous — Thu, 16 Oct 2014 17:51:27 GMT

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

Re: Group By ? Invalid Expression Error

Thu, 16 Oct 2014 18:08:02 GMT

getting Error for above expr ...

Aggregation Expressions required by groupby clause ?

Re: Group By ? Invalid Expression Error

Anonymous — Thu, 16 Oct 2014 18:20:17 GMT

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,

Re: Group By ? Invalid Expression Error

preminqlik — Thu, 16 Oct 2014 18:43:44 GMT

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;

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;

Re: Group By ? Invalid Expression Error

Fri, 17 Oct 2014 05:51:13 GMT

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

Re: Group By ? Invalid Expression Error

preminqlik — Fri, 17 Oct 2014 10:05:46 GMT

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

Re: Group By ? Invalid Expression Error

simondachstr — Fri, 17 Oct 2014 10:09:31 GMT

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

Re: Group By ? Invalid Expression Error

Fri, 17 Oct 2014 13:44:31 GMT

HI,

Debtors Outstanding Amount is Fine...

For Over Due Amount, I need to get Outstanding amount on from based on OST_DUE_DT Based on each & every transaction..