Abinitio Interview Question 7


The Input DataSet is as follws:

Acc_No, Acc_holder, Credit_month,Credit_Amount      month_no
001, xxxx, JAN, 1000 1
001, xxxx, FEB, 500 2
002, yyyy, JAN, 800             ===> 1
002, yyyy, AUG, 2300 8
002, yyyy, DEC, 500 12
003,    zzzz, May, 200 5

Problem Statement:
For each acc holder the credit month and the 
corresponding credit amount will be stored. 
It's not necessary to have the amount for all the 12 
months and in case of missing ,
value '0' needs to be populated. 

We need one record per acc_holder in the output with 
12 months data along with the total. 

Steps for Approach:


1. Generic the missing #s -> Normalize (12) 
2. Aggregate the records -> Rollup


Expected O/p: 

Acc_No |Acc_holder      | monthly_amt_summary           |    Total 
001 | xxxx | 1000 ,500,0,0,0,0,0,0,0,0,0,0|    1500
002 | yyyy  | 800 ,0,0,0,0,0,0,2300,0,0,0,500|    3600
003 |   zzzz |  0,0,0,0,200,0,0,0,0,0,0,0|            200



Design:
                            Key(Acct No)
                      --->sort->dedupsort--Normalize----->
                      |                                                           |
Input:Replciate                                                          JOIN; Key(AcctNo+MonthNo)-->Rollup-->Output
                      |                                                           |
                      ---Reformat----------------------------->



type temporary_type=
record
decimal("") cnt;
string("") amt_summary;
end;

temp::initialize(in)=
begin
temp.cnt::1;
temp.amt_summary::'';
end;

out::rollup(temp,in)=
begin
temp.amt_summary::if(temp.cnt==1) in.Credit_Amount else string_concat(temp.amt_summary,',',in.Credit_Amount);
temp.cnt::temp.cnt+1;
end;

out::finalize(temp,in)=
begin
out.*::in.*;
out.monthly_amt_summary::temp.amt_summary;
end;


Expected O/p: 

Acc_No | Acc_holder | monthly_amt_summary|     Total 
001 | xxxx | 1000 ,500,0,0,0,0,0,0,0,0,0,0| 1500
002 | yyyy  | 800 ,0,0,0,0,0,0,2300,0,0,0,500|         3600
003 |    zzzz         |    0,0,0,0,200,0,0,0,0,0,0,0|                   200


outputDML;
record
string("|") Acc_No;
string("|") Acc_holder;
string("|") yearly_amt_summary;
decimal("\n") Total;
end;


For other Abinitio Youtube video please visit my channel ->  DataPundit

For other peace and meditation videos please visit my channel -> @paradisetracks



Comments

Post a Comment

Popular posts from this blog

Abinitio Interview Question # 1 - Write Multiple Files in Abinitio

Image
 Question :  I get records of various region in huge volume ,  we want to write the records in region specific files.   How to achieve that? View the explanation in the below video: Answer: -WMF WMF: filename::get_filename(in)= begin let string("") l_filename=string_substring(in.data.region); filename::string_concat($AI_SERIAL,"/",l_filename.dat); end; out::reformat(in)= begin out::in.data; end; OUTPUT DML-: DML record string(int) filename; string(integer(4)) file_contents; end; RMF: filename::get_filename(in)= begin filename::string_concat($AI_SERIAL,"/",in.filename); end; out::reformat(read,filename,in)= begin out.file_contents::read; out.filename::filename; end; Please view my youtube channel  DataPundit
Read more
Image
  Metaprogramming in Abinitio 1. How to create output DML dynamically based in INPUT DML 2. How to create intermediate transform( XFR) dynamically based on INPUT DML      Please See my you tube video for explanation : PART 1 PART 2 For Example, let us say, we have an Input DML as below-: cat input.dml record string(",") name; decimal(",") age; decimal(",") salary; string(",") aadharid; date("yyyy-mm-dd")(",") joiningdate; string(",") mobileno=NULL; string(",") dlno=NULL; string(",") passportnumber=NULL; end; (1.1) prepare a new DML based on input DML i.e. add fields, remove fields, modify type Graph based parameters: ===================================================================== input_dml=$DIR/input.dml  [interpretation = constant] input_dml_info =$[read_type(input_dml)]   [interpretation = pdl] /* Define functions in AB_DML_DEFS */ AB_DML_DEFS= $[ out:: dynamic_dml_gen(input_dml_info)= beg...
Read more

Next In Sequence in ABinitio | How next_in_sequence() works in MFS

Image
https://youtu.be/JdDDpjZcv-4 next_in_sequence() working in Abinitio: Definition -      within a single process(component) , this gives successive number.   next_in_sequence()>1 i.e. all records whose occurance is 2 and more in the file.      for example dataset below:    employeeName ram shyam ravi hanuman shekhar seeta geeta shayama vidushi vishakha If we process by condition next_in_sequence()>1 then the output will be [first record will be removed] shyam ravi hanuman shekhar seeta geeta shayama vidushi vishakha 2. next_in_sequence() with reformat  - rely -  FieldA 1a 2a 3a 4a 5a 6a 7a Example 1 (serial processing)--> Reformat   count=2      select:   next_in_sequence()<900      Transofom1:   out:reformat(in)=   begin   out.*::in.*;   out.Seq-No::next_in_sequence();   end;      Transform2:   out:reformat(in)=   begin   out.*:...
Read more