topic Re: Fetching individual data not using sum in QlikView

Fetching individual data not using sum

Fri, 29 May 2015 03:26:26 GMT

Hello community

I got this code to calculate this:

My key is CUST_NO to find the customer and check the the TRANS_STOCK_QTY and have the number inverted. Then, I check whether it is >= Group Minimum Order Quantity (GroupMOQ) or not. if it is I give 1 to HitMOQCount otherwise 0.

Calculation:

LOAD

CUST_NO,

GroupName,

GroupMOQ,

sum(TRANS_STOCK_QTY*-1) as TotalbyGroup

Resident Fact

Group By

CUST_NO,

GroupName,

GroupMOQ;

DROP Table Fact;

Calculation1:

Load

if(TotalbyGroup >= GroupMOQ,1,0) as HitMOQCount

Resident Calculation;

DROP Table Calculation;

Problem:

the expression sum(TRANS_STOCK_QTY*-1) as TotalbyGroup aggregates all of the TRANS_STOCK_QTY. But I need individually. Any suggestion please let me know

Thanks in advance everyone

Re: Fetching individual data not using sum

CELAMBARASAN — Fri, 29 May 2015 04:00:19 GMT

Expecting this?

Calculation:

LOAD

CUST_NO,

GroupName,

GroupMOQ,

(TRANS_STOCK_QTY*-1) as TotalbyGroup,

if((TRANS_STOCK_QTY*-1)>= GroupMOQ,1,0) as HitMOQCount

Resident Fact;

Re: Fetching individual data not using sum

Fri, 29 May 2015 04:18:18 GMT

I get the following error:

Aggregation expressions required by GROUP BY clause

Calculation:

LOAD

CUST_NO,

GroupName,

GroupMOQ,

(TRANS_STOCK_QTY*-1) as TotalbyGroup,

if((TRANS_STOCK_QTY*-1)>= GroupMOQ,1,0) as HitMOQCount

Resident Fact

Group By

CUST_NO,

GroupName,

GroupMOQ

Re: Fetching individual data not using sum

timanshu — Fri, 29 May 2015 05:09:13 GMT

Dont use group by then.

Just write only what celambarasan wrote.