Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Fetching individual data not using sum

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

3 Replies
CELAMBARASAN
Partner - Champion
Partner - Champion

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;

Not applicable
Author

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

timanshu
Creator III
Creator III

Dont use group by then.

Just write only  what  celambarasan  wrote.