Scenario

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

Kamna

  • Mar 27th, 2015
 

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

  Was this answer useful?  Yes

Soumita

  • 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

Preeti

  • 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.

-Best
Preeti

  Was this answer useful?  Yes

Rupali

  • 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

gouse

  • 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

ahamed

  • 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

Ryan

  • 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

mohankrishna

  • 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

ahamed

  • May 12th, 2016
 

inputfile-->rollup-->outputfile(rollup key is {cust_card_no,item})
in the transform write
out.cust_card_no::in.cust_card_no
out.item::in.item
out.count::count(in.item)
out.sum_amt::sum(in.amount)

  Was this answer useful?  Yes

Naveen

  • 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 =
record
decimal(",") sum_temp;
end;
temp :: initialize(in) =
begin
temp.sum_temp :: 0;
end;
temp :: rollup(temp,in) =
begin
temp_vec = vector_append(temp_vec,in.item);
temp.sum_temp :: temp.sum_temp + in.amt;
end;
out :: finalize(temp,in) =
begin
out.id :: in.id;
out.item_cnt :: length_of(vector_sort_dedup_first(temp_vec));
out.tot_amt :: temp.sum_temp;
end;

  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=
record
string("") [int] item_vector;
decimal("") sum_of_amount;
end;
out::initialize(in)=
begin
out.item_vector :: allocate_with_defaults();
out.sum_of_amount :: 0;
end;
/*Do computation*/
out::rollup(temp,in)=
begin
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;
end;
out::finalize(temp,in)=
begin
out.id :: in.id;
out.count :: length_of(temp.item_vector);
out.price :: temp.sum_of_amount;
end;
Output file :
id count price
10001 2 141
10003 1 20
10002 2 127

  Was this answer useful?  Yes

UMA

  • 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) =
begin
out.cust_card_no:1: in.cust_card_no;
out.item:2: in.item;
out.total:: sum(in.cost);
out.count:: count(in.item);
end;
output dml as:
record
decimal(",") cust_card_no;
string(",") item;
decimal(",") total;
decimal("
") count;
//string("");
end
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