Seperate delimited data

[QSense]

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?

[#2]

Hi,

You can use subfield function.

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

Regards,

Janzen

[sivarajs]

Try by loading in script

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

[QSense]

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]

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]

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