Abinitio Normalize Use Cases part #1 | How to use Abinitio Normalize Use cases

See the Normalize youtube video session below:


   


 

              
Normalize Use Cases , There are following use cases where in NORMALIZE can be used.

(A) Business Granularity Changes - Real Practical Example.


inputs:
aseet_under_management  & amount & tr_dt         &key1&key2&key3 & auditcol1& auditcol2& auditcol3
TrA1 & 45K       & 01-02-2015                      &f1&s1&m1
TrA2 & 87K       & 03-03-2015                      &f2&s1&m1
TrA3 & 34K       & 07-03-2015                      &f2&s2&m1
TrA4 & 98K       & 06-04-2015                      &f1&s1&m1
TrA5 & 57K       & 03-06-2015                      &f2&s1&m1                      
TrA6 & 64K       & 09-08-2015                      &f2&s2&m1  

step1 sort by key1,key2,key3,tr_dt ascending
aseet_under_management  & amount & tr_dt         &key1&key2&key3 & auditcol1& auditcol2& auditcol3
TrA1 & 45K       & 01-02-2015                      &f1&s1&m1
TrA4 & 98K       & 06-04-2015                      &f1&s1&m1
TrA2 & 87K       & 03-03-2015                      &f2&s1&m1
TrA5 & 57K       & 03-06-2015                      &f2&s1&m1
TrA3 & 34K       & 07-03-2015                      &f2&s2&m1
TrA6 & 64K       & 09-08-2015                      &f2&s2&m1

scan - key -key1,key2,key3;


end;


aum  & amount & tr_dt         &diff  &prv_amount    &key1&key2&key3 & auditcol1& auditcol2& auditcol3
TrA1 & 45K       & 01-02-2015  0      45K            &f1&s1&m1
TrA4 & 98K       & 06-04-2015  64      45K            &f1&s1&m1
TrA2 & 87K       & 03-03-2015  0     87K            &f2&s1&m1
TrA5 & 57K       & 03-06-2015  92    87K            &f2&s1&m1
TrA3 & 34K       & 07-03-2015  0      34K            &f2&s2&m1
TrA6 & 64K       & 09-08-2015  155      34K            &f2&s2&m1


=====normalize==========

temporary_type=
begin
decimal("") prv_amount;
date("") prv_dt;
end;

out::out.initialize(in)=
begin
out.prv_amount=in.amount;
out.prv_dt::in.tr_dt;
end;

out::out.length(in)=
begin
out=in.diff +1;
end;

out::normalize(temp,in,index)=
begin
out.tr_dt:1:if(index<temp.diff + 1) temp.prv_dt +index;
out.tr_dt:: in.tr_dt;
out.amount:1:if(index<temp.diff) temp.prv_amount;
out.amount:: in.amount;
out.*::in.*;
end;


outputs:
aseet_under_management  & amount & tr_dt         &key1&key2&key3 & auditcol1& auditcol2& auditcol3
TrA1 & 45K       & 01-02-2015                      &f1&s1&m1
TrA1 & 45K       & 02-02-2015                      &f1&s1&m1
TrA1 & 45K       & 03-02-2015                      &f1&s1&m1
.
.
.
TrA4 & 45K       & 05-04-2015                      &f1&s1&m1
TrA4 & 98K       & 06-04-2015                      &f1&s1&m1

TrA2 & 87K       & 03-03-2015                      &f2&s1&m1
TrA2 & 87K       & 04-03-2015                      &f2&s1&m1
TrA2 & 87K       & 05-03-2015                      &f2&s1&m1
...
TrA2 & 87K       & 02-06-2015                      &f2&s1&m1
TrA5 & 57K       & 03-06-2015                      &f2&s1&m1  

TrA3 & 34K       & 07-03-2015                      &f2&s2&m1
TrA3 & 34K       & 08-03-2015                      &f2&s2&m1
TrA3 & 34K       & 09-03-2015                      &f2&s2&m1
...
TrA3 & 34K       & 08-08-2015                      &f2&s2&m1                    
TrA6 & 64K       & 09-08-2015                      &f2&s2&m1  

 

(B) Flatten the JSON data or XML data

Work to Normalize the JSON or XML data
json-to-dml  <>  <> <> -base_element  json schema/json file
xml-to-xml   .xsd

length_of(json[2].labels);
color_label :: json[2].labels[0].color);




(C) For Lookthru - Repeat Fund Dimension hierarchy

Abinitio Design Solution - Using Conduct It and Normalize|Practical solution for a business Problem


  Fundid FundFundName parentfund  AssetAmt
  F1   x1        -                1000
  F2   x2       F1                2098
  F3   x3       F2                3400
  F4   x4       F2                87932
  F5   x5       F1                76543
  F9   X9       F1              0
  F6   x6       F3              500
  F7   x7       F3              300
  F8   x8       F3              200
 
  -------------------------lkpDataSet----
  F1        F2    80%     0
  F1        F5    10%     0
  F1        F9    10%     0
  F2        F3    50%     1
  F2        F4    50%     1
  F3        F6    60%     2
  F3        F7    30%     2
  F3        F8    10%     2

First Pass-:
input_<ab_plan_loop_current_value>-1.dat

I/P -> Normalize - length = lookup_count() +1 ; lookup_nth(lkpDataSet,in.FundID) ->
 rollup(parent,child)->o/p; output_1.dat
level1 - 0

F1   x1       -        1000
F2   X2      F1        800
F5   x5      F1        100
F9     X9      F1        100
F2   X2      F1        2098
F3   x3      F2           3400
F4   x4      F2           87932
F5   x5      F1           76543
F9   X9      F1          0

F1   x1       -        1000
F2   X2      F1        800 + 2098
F5   x5      F1        100 + 76543
F9     X9      F1        100 + 0
F3   x3      F2           3400
F4   x4      F2           87932
F5   x5      F1           76543
F9   X9      F1          0

2nd  Pass-:
(output of 1st pass)
I/P -> Normalize - length = lookup_count() +1 ; lookup_nth(lkpDataSet,in.FundID) -> rollup(parent,child)->o/p;
level1 - 0

Conduct>It

Graph task -> Looping Plan - For LOOP (depthnof the tree) ->



WITH FundHierarchy  AS (
-- root fund    --
    SELECT  0 AS [Level]
            ,R.FundID
            ,R.ParentFundFundID
            ,R.FundName
      FROM dbo.[FundDimension] AS R      
    WHERE  R.ParentFundFundID IS NULL
 
UNION ALL
-- Recursive call from normal fund --
    SELECT  FH.[Level] + 1
            ,F.FundID
            ,F.ParentFundID
            ,F.FundName
      FROM dbo.[FundDimension] AS F      
      JOIN FundHierarchy FH ON F.ParentFundID = FH.FundID
)
 
SELECT FundID
    ,ParentFundID
    , FundName       
    ,[Level]
FROM FundHierarchy;


Scan , Rollup , normalize use case, normalize scenerios, recursive query,
conduct It,Plan,looping plan,sequenctial looping plan in abinitio










(D)  Normalize yearly data

 

 Input : Yearly fin data in a record.
field1|field2|year|month1|revenue1|month2|revenue2|...month12|revenue12
field1|field2|year|value1|value2|value3|...........value2
act1|tr1|2021|jan=3400|feb=4500|mar=3200|........|dec=9087
act2|tr2|2020|jan=3403|feb=4500|mar=3200|........|dec=9087


field1|field2|year|month|amount
i/p dml-
record
string("|") filed1;
string("|") filed2;
string("|") year;
string("\n") restofrecs;
end;
================================
type month_aum_vec=
record
string(int) month;
string(int) aum;
end;



let month_aum_vec[12] my_vec =allocate_with_defaults();

out::length(in)=
begin
let int i=0;
my_vec=for(i,i<12):[record month string_split(string_split(in.restofrecs,'|')[i],'=')[0] aum string_split(string_split(in.restofrecs,'|')[i],'=')[1]];
out::length_of(my_vec);
end;

out:normalize(in,index)=
begin
out.filed1::in.filed1;
out.filed2::in.filed2;
out.year::in.year;
out.month::my_vec[index].month;
out.month::my_vec[index].aum;
end;


=====================================
record
string("|") filed1;
string("|") filed2;
string("|") year;
string("|") month;
string("\n") aum;
end;


 
(E) Directory listing -list the files and followed by RMF


    let directory_listing_type file_vector=
    directory_listing("Direcotry_path","*.csv",item_types="file");
    
    out::length_of(directory_listing_type);
    
    
    out.file_FundName::string_concat("dire_path","/",directory_listing_type[index]);

Other Normalize part 1 video can be see here:





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