Populate Multiple Fields From File

[dsharmaqv]

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.

[avinashelite]

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 ?

[dsharmaqv]

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

[dsharmaqv]

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

[avinashelite]

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.