Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
dsharmaqv
Creator III
Creator III

Populate Multiple Fields From File

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

FullPathTeamBalance
FIN/IND/Portfolio-1A1222
GBM/ASP/IND/Portfolio-9/Trader-2C1111
RB/EUR/FRA/City-1/Trader-6B1555
EQ/ASP/SL/Portfolio-10/City-3/Trader-9D1333

Target Format

Business FunctionRegionCountryPortfolioCityTrader nameTeamBalance
FIN-INDPortfolio-1--A1222
GBMASPINDPortfolio-9-Trader-2C1111
RBEURFRA-City-1Trader-6B1555
EQASPSLPortfolio-1AbcCity-3Trader-9D1333

Please help me out in finding the solution.

4 Replies
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
Creator III
Creator III
Author

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
Creator III
Creator III
Author

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.