Building Header/Body/Trailer Data In Abinitio

 In Some situations you need to transform data from a homogeneous format to header, body and trailer format.For example, If data from the graph are fed back to the legacy system.

The Youtube video can be watched here:

For example , input data is sample as follows:

We have lets say 8 records in input data, it could be many more records based on the requirements.

         

The DML defined for this data as below:

record

string("\t") Transaction_ID=NULL("");

string("\t") CustNM=NULL("");

string("\t") Trans_Date=NULL("");

string("\t") ProductName=NULL("");

string("\t") Quantity=NULL("");

string("\n") Transaction_Amt=NULL("");

end;

 note: records are based on Transaction_ID and CustNM i.e. we need to produce the header /boy /trailer based on Transaction_ID and CustNM.

 

 

Lets decide what columns we try to keep in each sections, for example here:

 

Header
Transaction_ID,CustN,total_items

Body(Data)
ProductName, Quantity,Transaction_Amt


Trailer
total_items,total_cost,Trans_Date

 

 

Now , we can think of intermediate format of the data as below:

 H = header and Ordering =1

B= body and ordering =2

T= trailer and ordering=3

note: we have choose 1,2,3 as we need sorting in order of display on the output and H,B,T as alphanumeric does not give display sorting as Abinito understands it.

 Reformating header body trailer record to generic formating as below in the table.

Following Abinito graph can be created to solve this:

 

1. Upper flow is to keep the body data as it is as we do not have any derived column in body(data) section

   It adds  a type field with a value B and an ordering field with value 2

2. Lower flow is to produce basically the rolled up columns for trailer and header respectively.

3. Reformat (count=2) in lower flow make sure the needed columns are chosen for respective sections

    It has to transforms

   Transform0 -  Adds a type field with a value T and an ordering field with value 3

   Transform1 -  Adds a type field with a value H and an ordering field with value 1

4. Merge (key=Transaction_ID and CustNM) make sure the data assembles in order of header, body and trailer order as per the group Transaction_ID and CustNM

 

Output DML can be created as below:

====
include "$AI_DML"
metadata_type=
record
begin
string(1) type,
 if(type=='H')
  header header_rec;
 if(type=='B')
  body body_rec;
 if(type='T')
  trailer trailer_rec;
end;

============
$AI_DML/output.dml
type header =
begin
string(",") Transaction_ID;
string(",") CustNM;
decimal("\n") total_items;
end;
type data =
begin
string(",") ProductName;
decimal(",") Quantity;
decimal("\n") Transaction_Amt;
end;

type trailer =
begin
Date("DDMMMYYYY")(",") Trans_Date;
string(",") total_items;
decimal("\n") total_cost;
end;

 

 

 

5. Final Reformat to make it is best viewed in format of vectors 

    12 records

 

Other recommended video for you to watch out:

 

 

 please visit my youtube channel for data engineering videos such as Abinito, UNIX, SQL, AWS, DWH etc

by Datapundit DATAPUNDIT