topic Re: Populate Multiple Fields From File in QlikView

Populate Multiple Fields From File

dsharmaqv — Mon, 01 Aug 2016 08:34:36 GMT

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.

Re: Populate Multiple Fields From File

avinashelite — Mon, 01 Aug 2016 08:57:03 GMT

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 ?

Re: Populate Multiple Fields From File

dsharmaqv — Mon, 01 Aug 2016 09:01:04 GMT

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

dsharmaqv — Mon, 01 Aug 2016 09:43:53 GMT

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

avinashelite — Mon, 01 Aug 2016 12:20:03 GMT

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.