Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

dsharmaqv
Contributor 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

Re: Populate Multiple Fields From File

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
Contributor III

Re: Populate Multiple Fields From File

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
Contributor III

Re: Populate Multiple Fields From File

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

Re: Populate Multiple Fields From File

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.

Community Browser