robmuza

 Profile Answers by robmuza Questions by robmuza

• Jul 10th, 2008

 

Scan is really the most simple way to achieve this.  Another way is to use a ROLLUP, since it is a multistage component.  You need to put the ROLLUP component into multistage format and write the intermediate results to a temp array (I think they're called vectors in AI).  The ROLLUP loops through each record in your defined group.

Let's say you want to get intermediate results by date.  You sort your data by {ID; DATE} first.  Then ROLLUP by {ID}.  The ROLLUP will execute it's transformation for each record per ID.  So store your results in a temp vector, which will need to be initialized to be the size of your largest group.  Each time the ROLLUP enters the tranformation, write to the [i] position in the array and increment i each time.  As long as this is all done in the "rollup" transformation and not the "finalize" transformation, it will run the "initialize" portion before it moves to the next ID.

I have done it this way, but the Scan is easier.  I was doing a more simple rollup before I found that I needed cumulative intermediate results, so I just modified my existing ROLLUP.  Ab Initio documentation does not explain this technique in detail, but it can be done.  Let me know if you need more detail and I can provide a better example.

abhi_fresh_as_dew

 Profile Answers by abhi_fresh_as_dew 

• Oct 10th, 2008

 

Use in built componenet in Abinitio .. "SCANWITHROLLUP"

Abhisek B
fresh as dew!!

anujaja

 Profile Answers by anujaja 

• Jan 27th, 2009

 

There are three ways
1) You can use Scan with rollup component
2) Use Rollup component
3) You can also use Scan followed by Dedup sort and select the last record. That will solve the purpose

sixto.dsilva

 Profile Answers by sixto.dsilva Questions by sixto.dsilva

• Dec 6th, 2009

 

Other then scan, we can use rollup to do the cumulative summary.

abmaahi

 Profile Answers by abmaahi Questions by abmaahi

• Mar 29th, 2010

 

We can use rollup component.

gcs7788

 Profile Answers by gcs7788 

• Dec 20th, 2014

 

@ robmuza,
I have a question for ... Please let me know if it is possible
I have deptno, sal in the emp table now i want 3rd highest salary in dept wise for say if i have sort like this

deptno sal
10 4000
10 3000
10 2000
10 1000
20 4000
20 2050

Now , i need 3rd highest salary in each group and if i dont have more than 3 records than i want to display first highest salary

Devendra Majhi

• Sep 21st, 2015

 

1. Use a Sort component to sort on {Deptno,sal Descending}
2. Use Reformat to assign ranks to salary for each group as below
let string(|) t_dept="";
let decimal(|) t_sal=0;
let decimal(|) t_rank=0;
/*Reformat operation*/
out::reformat(in) =
begin
t_rank=if(t_dept!=in.dept)1 else if(t_sal!=in.sal)t_rank+1 else t_rank;
t_dept=in.dept;
t_sal=in.sal;
out.rank::t_rank;
out.dept::in.dept;
out.sal::in.sal;
end;
3. Then use filter transformation to filter out rank==3. You can use variable wherein you can pass nth highest salary

ravichcv85

 Profile Answers by ravichcv85 

• Dec 28th, 2015

 

1. First use sort with key as dept # and sal (descending) - such that highest salary will be on top.

2. Use DEP # as the key and pass the sorted data into SCAN.In SCAN use a temporary variable i and keep incrementing it for each record within a group.When I=O save the salary (this is the highest salary) - and if i=2 (mean there is 3rd record or 3rd highest salary in the group) if we reach within the group then replace the salary saved when i=o by the salary when i=2 (this is the third salary) - and in finalize assign this saved salary to output.

so if i=2 is not reached - means we do not have 3rd highest salary - in that case the saved salary would be the highest salary which will be assigned in finalize.

Dinesh

• Jan 5th, 2016

 

It can be done using Rollup
input_file --> sort --> rollup --> output
type temporary_type=record
decimal("") rank ;
decimal("") sal ;
end; /*Temporary variable*/
temp :: initialize(in) =
begin
temp.rank :: 1;
temp.sal :: in.sal;
end;
temp :: rollup(temp, in) =
begin
temp.sal:1: if(temp.rank <= 3) in.sal else temp.sal ;
temp.rank :: temp.rank+1;
end;

out :: finalize(temp, in) =
begin
out.sal:: temp.sal;
out.dept_no :: in.dept_no;
end;

disha

• Jan 22nd, 2016

 

@Dinesh: but it is not taking care of the requirement, i.e. if there is no 3rd record, it should display the first highest sal. In the solution you mentioned it will display the second highest sal.

mk_44

 Profile Answers by mk_44 

• Feb 9th, 2016

 

I guess below code can help for 3rd max else 1st max for each group key.
Input_file --> sort (descending) --> Rollup --> output
type temporary_type=record
decimal("") rank ;
decimal("") sal ;
end;
temp :: initialize(in) =
begin
temp.rank :: 0;
temp.sal :: in.sal; 2200
temp.max_sal::in.sal; 2200
end;
temp :: rollup(temp, in) =
begin
temp.rank :: temp.rank+1;
temp.sal:: if((temp.rank ==1||(temp.rank==3)) in.sal else temp.sal ;
end;
out :: finalize(temp, in) =
begin
out.sal:: temp.sal;
out.dept_no :: in.dept_no;
end;