topic Re: Need Help on Transform Data with Specific Format in QlikView

Need Help on Transform Data with Specific Format

mmmm2016 — Wed, 25 Nov 2020 16:16:04 GMT

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

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

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.

Re: Need Help on Transform Data with Specific Format

marcus_sommer — Wed, 15 Jun 2016 11:47:08 GMT

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

Re: Need Help on Transform Data with Specific Format

cwolf — Wed, 15 Jun 2016 12:00:07 GMT

see example.

- Christian

Re: Need Help on Transform Data with Specific Format

florentina_doga — Wed, 15 Jun 2016 12:09:42 GMT

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;

Re: Need Help on Transform Data with Specific Format

mmmm2016 — Thu, 16 Jun 2016 10:43:27 GMT

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

Re: Need Help on Transform Data with Specific Format

mmmm2016 — Thu, 16 Jun 2016 10:45:01 GMT

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

Re: Need Help on Transform Data with Specific Format

mmmm2016 — Thu, 16 Jun 2016 10:48:15 GMT

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