Cummulative summary in AB Initio

How can I achieve cummulative sumary in AB Initio other than using SCAN component. Is there any inbuilt function available for that?

Questions by abinitio17

Showing Answers 1 - 6 of 6 Answers


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.

  Was this answer useful?  Yes

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

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

  Was this answer useful?  Yes

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

  Was this answer useful?  Yes

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.

  Was this answer useful?  Yes

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.

  Was this answer useful?  Yes

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;

  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