topic Seperate delimited data in QlikView

Seperate delimited data

QSense — Tue, 06 Nov 2012 09:57:27 GMT

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?

Re: Seperate delimited data

Tue, 06 Nov 2012 10:12:05 GMT

Hi,

You can use subfield function.

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

Regards,

Janzen

Re: Seperate delimited data

sivarajs — Tue, 06 Nov 2012 10:37:48 GMT

Try by loading in script

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

Re: Seperate delimited data

QSense — Tue, 06 Nov 2012 12:04:46 GMT

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

Re: Seperate delimited data

sivarajs — Wed, 07 Nov 2012 05:30:37 GMT

Mapping

LOAD * Inline [

new_id, Name

100, Alex

101, David

];

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

ID_Name , Name

100;101 ,------

100, Alex

101, David

];

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

Re: Seperate delimited data

sujeetsingh — Wed, 07 Nov 2012 06:10:41 GMT

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.....