topic Re: Split token Index in QlikView

Split token Index

Wed, 05 Mar 2014 08:01:43 GMT

Hi all, I'm using Split function without second parameter in order to parse a column like this

abc | def | ghi

xxx| yyy | zzz

LOAD

SubField(col,'|') as col1,

col

resident S;

It creates a table with 3 rows

abc

def

ghi

and I would like to add a second column with index starting at 1 for each original row ..

abc 1

def 2

ghi 3

xxx 1

yyy 2

zzz 3

Do you know how to do it?

Thanks!

Re: Split token Index

MK_QSL — Wed, 05 Mar 2014 08:07:20 GMT

LOAD

RowNO() as NO,

SubField(col,'|') as col1,

col

resident S;

UPDATE :

Load * Inline

[

col

abc | def | ghi

xxx| yyy | zzz

];

FINAL:

LOAD

RowNo() as ID,

TRIM(SubField(col,'|')) as col1,

col

resident S;

Re: Split token Index

Wed, 05 Mar 2014 08:17:19 GMT

Thanks for your answer but it does not start in 1 for each original row so I obtain

abc 1

def 2

ghi 3

xxx 4

yyy 5

zzz 6

instead of

abc 1

def 2

ghi 3

xxx 1

yyy 2

zzz 3

Re: Split token Index

MK_QSL — Wed, 05 Mar 2014 08:18:23 GMT

Check my enclosed file which i have updated later on...

Re: Split token Index

Wed, 05 Mar 2014 08:27:09 GMT

But I need the correct value in the "database" if you look in your file ID column doing Ctrl+Tyou will see 1,2,3,4,5,6

Re: Split token Index

tresB — Wed, 05 Mar 2014 08:45:30 GMT

For ID calculation try this in Manish's sample app:

Load

If(col=peek('col'),peek('ID')+1, 1) as ID

Re: Split token Index

MK_QSL — Wed, 05 Mar 2014 09:12:37 GMT

Like this?

Re: Split token Index

jagan — Wed, 05 Mar 2014 09:48:14 GMT

Hi,

Try this scirpt

Data:

LOAD

If(Text <> Previous(Text), 1, peek('ID')+1) as ID;

LOAD

Text,

SubField(Text, '|') AS Value

INLINE [

Text

abc | def | ghi

xxx| yyy | zzz];

Regards,

Jagan.

Re: Split token Index

Wed, 05 Mar 2014 11:39:59 GMT

This is one more variant of solution:

Data:

Load * Inline

[

col

abc | def | ghi

xxx| yyy | zzz

];

LOAD

-AutoNumber(col)+AutoNumber(col1) as ID,

LOAD

TRIM(SubField(col,'|')) as col1,

col

resident Data;

Re: Split token Index

maxgro — Wed, 05 Mar 2014 20:51:11 GMT

load col, SubField(col, '|') as s, if(col<>peek(col), 1, peek(id)+1) as id;

LOAD * Inline [

col

abc | def | ghi

xxx| yyy | zzz

];

drop field col;