Qlik Community

Qlik DataMarket Discussions

Discussion Board for collaboration regarding Qlik DataMarket.

dsharmaqv
Contributor III

Populate Multiple Fields From another Field

Hi

I have a file which is not in apprpriate formate and I want to change the file as per the reporting requirement.

Data is not in order due to missing values.

For Ex:

In first record

Region City and trader name is missing.

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.

Note:

5 Replies

Re: Populate Multiple Fields From another Field

What is the logic behind finding which field is missing.

Regards,

Kaushik Solanki

dsharmaqv
Contributor III

Re: Populate Multiple Fields From another Field

Unfortunately, there is no logic behind that. The only thing is that all the value appears in series if velue is there

Series

Business Function/Region/Country/Portfolio/City/Trader Name.

Re: Populate Multiple Fields From another Field

FIN/IND/Portfolio-1

GBM/ASP/IND/Portfolio-9/Trader-2

So from above data how system should know what value is what. For example you said from above data(1st Case) region is missing, how system should know, because system will consider IND as region because its second value.

Hope u understood what I want to say.

Regards,

Kaushik Solanki

dsharmaqv
Contributor III

Re: Populate Multiple Fields From another Field

I understood your question and have posted this query to look for some work around to get the desiderd result.

galax_allu
Valued Contributor

Re: Populate Multiple Fields From another Field

Hi

you can try this (Not given exact solution).

Please go around with  FindOneOf( and SubStringCount(  like that ..

What i found is , if you have count of character '/" greater than 5 times only we can able to split...

SOURCE:

LOAD

  

    FullPath,

    Team,

    Balance

FROM

[https://community.qlik.com/thread/227340]

(html, codepage is 1252, embedded labels, table is @1);

NOConcatenate

TEMP1:

LOAD *,

    SubStringCount(FullPath,'/') as char_count,

    FindOneOf(FullPath,'/') as exactplace,

      FindOneOf(FullPath,'/',2) as exactplace2,

      Len(FullPath) as fullpathlen

    Resident SOURCE;

  

    DROP Table SOURCE;

  

    NOConcatenate

RESULT:

LOAD *,

if( char_count>=5, LEFT(FullPath,exactplace-1)) as BusinessFuntion,

if( char_count>=5, MID(FullPath,exactplace,(exactplace2- exactplace))) as Region

Resident TEMP1;

DROP TABLE TEMP1;

Community Browser