Need Help on Transform Data with Specific Format

[mmmm2016]

Hi All

I am new to qlikview so need help...

I have a scenario with input data like this-

ID
12-4-1/12-4-17 12-4-1/13 12-4-7/USP-32 8-6-27/12-4-14/18 8-6-27/41/5 8-6-47/63/28/15

I want to transform the field ID as below~

ID
12-4-1/12-4-17 12-4-1/12-4-13 12-4-7/USP-32 8-6-27/12-4-14/12-4-18 8-6-27/8-6-41/8-6-5 8-6-47/8-6-63/8-6-28/8-6-15

I have tried for many days and have no ideas.

Thank you in advance.

[marcus_sommer]

I think I would split the ID by it's delimiter of '/' per subfield() within a load and would try to find any logic which satisfied the needs, probabyl with nested if-loops which checks if the value is numeric and/or the len from the value and so on from those values which belong together (maybe you need to grab these values per Peek() or Previous() ?:

t1:

load recno() as RecNo, rowno() as RowNo, subfield(ID, '/') as ID_Split, ID From YourSource;

t2:

load if(..... resident t1;

- Marcus

[cwolf]

see example.

- Christian

[florentina_doga · Accepted solution]

i have a solution. try this

aa:

load * inline [id

12-4-1/12-4-17

12-4-1/13

12-4-7/USP-32

8-6-27/12-4-14/18

8-6-27/41/5

8-6-47/63/28/15

];

NoConcatenate

load *,

    Index(id,tt) as yy;

bb:

load id,

SubField(id,'/') as tt,

recno() as rand

resident aa;

drop table aa;

NoConcatenate

load *,

if(id=previous(id),peek('fiiii')&'/'&final,final) as fiiii;

cc:

load *,

if(len(fin)=0,test&tt,tt) as final;

load *,

    if(rand=previous(rand) and len(fin)=0,peek(previous('test')),fin) as test;

load *,left(tt,len(tt)-len(subfield(tt,'-',-1))) as fin

resident bb order by rand asc, yy asc;

drop table bb;

left join

load id,

max(len(fiiii)) as max_text

resident cc group by id;

//exit script;

NoConcatenate

dd:

load

    id,

    fiiii as id_new,rand

resident cc where len(fiiii)=max_text

;

drop table cc;

[mmmm2016]

Oh Florentina, I'm sure you are a genius with IQ over 200!

[mmmm2016]

Hi Christian, thank you for your example! It's really very kind of you.

[mmmm2016]

Hi Marcus, thank you for your help. You are master of Qlikview.