Financial Report using xml (Part 2)

Part 2 is going to focus on how we take the data we transformed in part 1, and turn it into a tabular format

The first 10 lines of our file, the “Checks” file, is shown below:

1 ChS_Details chs_id CHS.000000000a
1 ChS_Details scheme @
1 ChS_Details processors 1
1 ChS_Details institutional_sector S3
1 ChS_Details check_type 9
1 ChS_Details operation_country PT
1 ChS_Details operation_division EUR
1 Operations quantity 8988
1 Operations amount 15412.53
1 Operations_Dev quantity 587

We executed the code below to process this data for each file.

cat TABULAR_FILES/$dirname/tmp-$filename  |
# --- Deals with an exception on specific fields called,
Operacoes, Imp_Legal, Operacoes_Dev, Dev_Operacoes_Dev,
due to different level in deepness of the tree
we use this command to artificially say
--- this level is (N-1) ---
awk '{if($2=="Operacoes"){print $1,$2,"Operacoes_"$3,$4}
else if($2=="Imp_Legal"){print $1,$2,"ImpLegal_"$3,$4}
else if($2=="Operacoes_Dev"){print $1,$2,"OperacoesDev_"$3,$4}
else if($2=="Dev_Operacoes_Dev"){print $1,$2,"DevOperacoesDev_"$3,$4}
else{print $0}}'           |
# --- Removes the 2nd field ---
delf 2                    |
# --- Maps file to tabular format ---
map -m@ num=1                 |
# --- Removes 1st field ---
delf 1              |
fcols --              > TABULAR_FILES/$dirname/$filename

In this block of code we use a set of commands to deal with the data, the most important of which is map. The map command re-formats the specified file into a matrix consisting of  row key fields,  column key fields and the rest of the fields as data fields. In the case under analysis, the map command is used to set the name of the data field as the first record followed by the values of each instance; an snippet of the result can be seen below:

divisa_operacao pais_operacao processador scheme setor_institucional tipo_transferencia trr_id
EUR             X             1           1      S7                  0                  TRR.0000000001
EUR             X             1           1      S6                  0                  TRR.0000000002
EUR             X             1           1      S2                  0                  TRR.0000000003
EUR             X             1           1      S5                  0                  TRR.0000000004
EUR             X             1           1      S2                  0                  TRR.0000000005
EUR             X             1           1      S6                  0                  TRR.0000000006
EUR             X             1           1      S6                  0                  TRR.0000000007
EUR             X             1           1      S5                  0                  TRR.0000000008
EUR             X             1           1      S2                  0                  TRR.0000000009

In part 3, we explain the process of validating data for missing fields and mismatch datatypes.

Find out more

Request a demo and speak with our team about how you can leverage the power of Unicage in your organization.

Privacy Policy