Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Hi,
You can use subfield function.
subfield(S, ';' ,2) returns 'cde' if S is 'abc;cde;efg'
Regards,
Janzen
Try by loading in script
load *, subfield(fieldname,';') as new_field
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
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
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.....