Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mmmm2016
Contributor
Contributor

Need Help on Transform Data with Specific Format

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.

1 Solution

Accepted Solutions
florentina_doga
Partner - Creator III
Partner - Creator III

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;

View solution in original post

6 Replies
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
Creator III
Creator III

see example.

- Christian

florentina_doga
Partner - Creator III
Partner - Creator III

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
Contributor
Contributor
Author

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

mmmm2016
Contributor
Contributor
Author

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

mmmm2016
Contributor
Contributor
Author

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