Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

qliekview
Contributor II

Seperate delimited data

Hi everyone,

In my access database, there is fiield showing employee's identiiy but it ıncludes more than one id with ";"

such as 100;101

İn qv side 100 id ' s name Alex but because of delimited data ı can not see the name of 100 ID

ID                         Name

100;101               ------

100                         Alex

101                          David

How can I seperate the delimited field in two different rows to see names?

Tags (1)
5 Replies
Not applicable

Re: Seperate delimited data

Hi,

You can use subfield function.

subfield(S, ';' ,2) returns 'cde' if S is 'abc;cde;efg'

Regards,

Janzen

sivarajs
Valued Contributor II

Re: Seperate delimited data

Try by loading in script

load *, subfield(fieldname,';') as new_field

qliekview
Contributor II

Re: Seperate delimited data

Hello

Your solution makes delimited fields into one column I wanna explain in an example

In my qw

ID                         Name              

100                        Alex

100;101                    --

102                         David

After your solution 100;101 fields are seperated but I wanna it in one column so ı can see the name of ID

sivarajs
Valued Contributor II

Re: Seperate delimited data

a:

Mapping

LOAD * Inline [

    new_id, Name

    100,  Alex

    101,  David

     

  ];   

b:

LOAD *, SubField(ID_Name,';')as new_id Inline [

ID_Name , Name

100;101 ,------

100,  Alex

101,  David

];

c:

NoConcatenate

LOAD *, ApplyMap('a',new_id) as name

Resident b;

drop Table b;

In actual you have many ID for that you can replace table a with excel sheet that have id and name

sujeetsingh
Honored Contributor III

Re: Seperate delimited data

Hi,

Just try this may be it help you....

1. if its sure that Your ID is of fixed lenght '3'

  Then use this -----

   =  if(len(ID_Name)=3,ID_Name,subfield(ID_Name,';'))

   

Else if it is not so Then,

    =if(substringcount(ID_Name,';')=0,ID_Name,subfield(ID_Name, ';'))

Else give me sample file.....

Community Browser