Kamna

• Mar 27th, 2015

 

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

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

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

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

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.

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

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)

Ryan

• Apr 4th, 2016

 

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

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

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)

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;

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

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.

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

Saikumar

• Aug 14th, 2019

 

Count scenario: output will be 2
--------------
Take Partition by key and sort component and key as "{item}" then after
Take Dedup sort component and key as "{item} and keep as "first" then after
Take Rollup Component and Key as {item}
Condition like as below:
type temporary_type = record
decimal("|") count;
end;
out::initialize(in) =
begin
out.count :: 0;
end;
out::rollup(tmp, in) =
begin
out.count :: tmp.count + 1;
end;
out::finalize(tmp, in) =
begin
out.cust_card_no :: in.cust_card_no;
out.item :: in.item;
out.count :: (tmp.count);

end;
Total amount spended by Customer scenario: output will be 80
-----------------------------------------
Take Rollup component and key as { Customer}
Condition like as below:
type temporary_type = record
decimal("|") sum;
end;
out::initialize(in) =
begin
out.sum :: 0;
end;
out::rollup(tmp, in) =
begin
out.sum :: tmp.sum + (in.amount);
end;
out::finalize(tmp, in) =
begin
out.cust_card_no :: in.cust_card_no;
out.item :: in.item;
out.Cusomer_spend_total_money :: (tmp.sum);
end;

anjali

• Sep 14th, 2020

 

car_no item count amount
1001 copy 3 60
1001 pen 2 20
is it the desired output ?

Shubam Salaria

• Aug 3rd, 2023

 

what does vector_search() func do?