I have customer details as below,

cust_card_no item amount
10001 pen 10
10001 copy 20
10001 pen 10
10001 copy 20
10001 copy 20
now my question is to find the distinct count of item per customer and total ammount spend by a customer?

Questions by Soumita Bhattacharya

Showing Answers 1 - 14 of 14 Answers


  • Mar 27th, 2015

Could you please let us know what is output expected for this problem?

  Was this answer useful?  Yes


  • Mar 30th, 2015

Output will be 2 for distinct count of item and 10+20+10+20+20=80 for total amount..and these two result should appear in a single output. Please help

  Was this answer useful?  Yes


  • Mar 30th, 2015

Hello Soumita,

What I have understood from your explanation I think the problem can be solved using a normalize and the a rollup component. First a normalize component will be used which will turn the rows in to column and the using a roll up component to calculate the count and the total amount can be put forth.

I am not sure of the correctness of the approach but that is again my understanding.

Let me know if you find the proper answer which is anything apart from this.


  Was this answer useful?  Yes


  • Mar 31st, 2015

I think by using rollup component.
set parameter:

key- {cust_card_no;item}
transform function:
assign distinct count to count(*);
assign total to sum(in.amount);

try it.
If not working ,let me know

  Was this answer useful?  Yes

Jyoti Agarwal

  • Sep 10th, 2015

Hi Soumita,
We can do this in a same window using a sort component and a rollup in expanded mode.

  Was this answer useful?  Yes


  • Oct 26th, 2015

Sort component, roll up key-item template mode-count(item), sum(in.amount)
if count(item)==1 i.e nothing but distinct value

  Was this answer useful?  Yes


  • Mar 21st, 2016

In Rollup keep the key is cust_id and transform like - >out.cust_id::in.cust_id, out.tot_amt::sum(in.amt)

  Was this answer useful?  Yes


  • Apr 4th, 2016

Sort on cust_card_no and item. Followed by rollup in expanded mode.
Rollup key should by cust_card_no

  Was this answer useful?  Yes


  • Apr 8th, 2016

You can do it in two ways. If you are comfortable with Rollups expanded view transformation we can achieve the output using single Rollup component as below.

Use the roll up with key as customer_id and in transformation use sting_concat to concat all the items purchased by a customer and create a temporary vector. In finalise sort this vector and dedup this vector and take count of the vector and assign it to output field count. This will be the count of distinct items purchased by customer. For total amount spent by customer you can use aggregate function sum().

Way2 -> use one rollup with raw data key as customer id you can get the sum of amount here. And in other flow sort the data on customer_id and item and dedup on same. Use rollup to this flow on key customer_id and count you get here is count of distinct items the customer has purchased. Now you need to join these two flows on customer_id and get the fields to output

  Was this answer useful?  Yes


  • May 12th, 2016

inputfile-->rollup-->outputfile(rollup key is {cust_card_no,item})
in the transform write

  Was this answer useful?  Yes


  • Nov 28th, 2016

We can do with Rollup component...
Graph Flow --> i/p file - rollup - o/p file.
Code is as below in Rollup
Key is id
let string(",") [int]temp_vec = allocate();
type temporary_type =
decimal(",") sum_temp;
temp :: initialize(in) =
temp.sum_temp :: 0;
temp :: rollup(temp,in) =
temp_vec = vector_append(temp_vec,in.item);
temp.sum_temp :: temp.sum_temp + in.amt;
out :: finalize(temp,in) =
begin ::;
out.item_cnt :: length_of(vector_sort_dedup_first(temp_vec));
out.tot_amt :: temp.sum_temp;

  Was this answer useful?  Yes

Ashutosh Prasad

  • Jan 30th, 2017

Input Data :
id item price
10001 pen 10
10001 copy 20
10001 pen 10
10003 copy 20
10002 copy 20
10001 pen 101
10002 pen 107
Use expanded rollup with key as {id} and use the below code:
type temporary_type=
string("") [int] item_vector;
decimal("") sum_of_amount;
out.item_vector :: allocate_with_defaults();
out.sum_of_amount :: 0;
/*Do computation*/
out.item_vector :: if (vector_search(temp.item_vector,in.item)==-1) vector_append(temp.item_vector,in.item) else temp.item_vector;
out.sum_of_amount :: temp.sum_of_amount + in.price;
begin ::;
out.count :: length_of(temp.item_vector);
out.price :: temp.sum_of_amount;
Output file :
id count price
10001 2 141
10003 1 20
10002 2 127

  Was this answer useful?  Yes


  • Jun 16th, 2017

I think for this above scenario output will be o records because if we want distinct records based on custid or item wise then no distinct records are present so output will be 0 records.

  Was this answer useful?  Yes

Shrinidhi SHetty

  • May 6th, 2019

Use Rollup component, and in transform specify
Key as {cust_card_no; item} and transform as
out :: rollup(in) =
out.cust_card_no:1: in.cust_card_no;
out.item:2: in.item; sum(in.cost);
out.count:: count(in.item);
output dml as:
decimal(",") cust_card_no;
string(",") item;
decimal(",") total;
") count;
You will get the desired output

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.


Related Answered Questions


Related Open Questions