Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I have a file which is not in apprpriate formate and I want to change the file as per the reporting requirement.
Source Format
FullPath | Team | Balance |
---|---|---|
FIN/IND/Portfolio-1 | A1 | 222 |
GBM/ASP/IND/Portfolio-9/Trader-2 | C1 | 111 |
RB/EUR/FRA/City-1/Trader-6 | B1 | 555 |
EQ/ASP/SL/Portfolio-10/City-3/Trader-9 | D1 | 333 |
Target Format
Business Function | Region | Country | Portfolio | City | Trader name | Team | Balance |
---|---|---|---|---|---|---|---|
FIN | - | IND | Portfolio-1 | - | - | A1 | 222 |
GBM | ASP | IND | Portfolio-9 | - | Trader-2 | C1 | 111 |
RB | EUR | FRA | - | City-1 | Trader-6 | B1 | 555 |
EQ | ASP | SL | Portfolio-1Abc | City-3 | Trader-9 | D1 | 333 |
Please help me out in finding the solution.
if you have the correct order for full path field try like this
subfield(FullPath,'/',1) as Business_Function,
subfield(FullPath,'/',2) as Region,
subfield(FullPath,'/',3) as Country,
subfield(FullPath,'/',4) as Portfolio,
subfield(FullPath,'/',5) as City,
subfield(FullPath,'/',6) as Trade_name
//but i could see your data is not in proper order , could you please confirm ?
Hi Avinash
Yes Data is not in order due to missing values.
For Ex:
In first record
Region City and trader name is missing.
Please suggest how to handle this.
Many Thanks in Advance
Hi Avinash
Yes Data is not in order due to missing values.
For Ex:
In first record
Region City and trader name is missing.
Please suggest how to handle this.
Many Thanks in Advance
One easy way is you ask the team population to for the order i.e.if data is missing then they need to create blank or null value like this
FIN/IND/////Portfolio-1
otherwise you need to build a mapping tables for all the 6 columns then check to which specific column it belongs to ..it would be bit length process flow.