topic Re: separating values by comma in QlikView

separating values by comma

Wed, 08 May 2013 15:22:25 GMT

Here is my sample data ,

Say i have values like

CatDogRat

HorsePig

How do i modify as below

Cat,Dog,Rat

Horse,Pig

Thanks

Radhika

Re: separating values by comma

flipside — Thu, 09 May 2013 10:07:46 GMT

Hi,

Assuming the words are identifiable by a starting Uppercase letter, then you can fix it as the following example ...

Strings:

load Rawtext, concat(String,',') as Fixedtext group by Rawtext;

load Rawtext, Split, mid(Split,iterno(),1) & TextBetween(Rawtext,mid(Split,iterno(),1),mid(Split,iterno()+1,1)) as String while iterNo() <= len(Split);

load Rawtext, PurgeChar(Rawtext,'abcdefghijklmnopqrstuvwxyz') as Split

inline [

Rawtext

CatDogRat

HorsePig];

flipside

EDIT: Hmmm, doesn't work if you have words beginning with same letter though.

Re: separating values by comma

flipside — Thu, 09 May 2013 10:53:57 GMT

Think this works better ...

Strings:

Load Replace(Rawtext, 'zzz','') as Rawtext, pass, Replace(String, 'zzz,','') as String;

Load Rawtext,

iterno() as pass,

if(iterno()=1,

Replace(Rawtext, chr(iterno()+64), ',' & chr(iterno()+64)),

Replace(peek('String'), chr(iterno()+64), ',' & chr(iterno()+64))) as String while iterNo() >=1 and iterNo() <= 26;

load 'zzz' & Rawtext as Rawtext inline [

Rawtext

CatDogRat

HorsePig

HorseHare

ZebraAntelope];

inner join

Load 26 as pass autogenerate 1;

Drop field pass;

flipside

Re: separating values by comma

sujeetsingh — Thu, 09 May 2013 11:05:40 GMT

Flip please explain the code ..

Re: separating values by comma

flipside — Thu, 09 May 2013 11:50:58 GMT

Of course.

First, some test data (the 'zzz' bit will become clear later)...

load 'zzz' & Rawtext as Rawtext inline [

Rawtext

CatDogRat

HorsePig

HorseHare

ZebraAntelope];

... then we need to look at each string and place a comma before the capital letters. I decided the easiest way was to loop over each capital letter, replacing them as needed with [comma+capital letter], so as a preceding load to the test data ...

Load Rawtext,

iterno() as pass,

if(iterno()=1,

Replace(Rawtext, chr(iterno()+64), ',' & chr(iterno()+64)),

Replace(peek('String'), chr(iterno()+64), ',' & chr(iterno()+64))) as String while iterNo() >=1 and iterNo() <= 26;

... this will expand the data, creating a row for each iterno() 1 to 26 (one for each letter of the alphabet). If it's the first iteration, then use the string, otherwise 'peek' the last value of the string. We need to pass the ASCII code for each letter which starts at 65 for uppercase A (or iterno() + 64) to 90 for uppercase Z. After this we only need the last pass, so we can inner join on a set value of pass (26) to get rid of pass values 1 to 25 ...

inner join

Load 26 as pass autogenerate 1;

... then we just need to eliminate the rogue comma introduced at the front of the string. I placed an identifier 'zzz' to make it simple using another Replace in a preceding load (you will see the text to remove in the string is 'zzz,' and not just 'zzz') ...

Load Replace(Rawtext, 'zzz','') as Rawtext, pass, Replace(String, 'zzz,','') as String;

... finally, remove the 'pass' field as not needed.

Drop field pass;

flipside

Re: separating values by comma

sujeetsingh — Thu, 09 May 2013 11:53:07 GMT

Nice !

thanks