Solved: Re: How to convert a single row into multiple base... - Page 3 - Qlik Community

shekhar_analyti · ‎2017-12-20

Hi All ,

How to convert a single row into multiple based on a delimiter condition in one particular column ?

Field Person has values delimited . But Delimiter type is not fixed .

Thanks & Regards

Shekar

sunny_talwar · ‎2017-12-20

Not sure how can you improve this, but may be this

SET vChange = Replace(Replace(Replace(Replace(

Replace(Replace(Replace(Replace(

Replace(Replace(Replace(

$1, '~', ','), '!', ','), '@', ','), '#', ','), '$', ','), '%', ','), '^', ','), '&', ','), '*', ','), '(', ','), ')', ','), '_', ','), '+', ','), '-', ','), '=', ','), Chr(39), ','), ';', ','), '"', ','), ':', ','), '}', ','), '{', ','),

']', ','), '[', ','), '\', ','), '|', ','), '?', ','), '>', ','), '<', ','), '.', ','), '/', ','), ' ', ',');

LOAD *,

SubField($(vChange(Person)), ',') as NewPerson;

LOAD * INLINE [

Doornbr, Date, Person

22456, 12/12/2017, "Sam,Krish,Tony"

22457, 13/12/2017, "Sam,Bill.Munoj"

22458, 14/12/2017, Murphy#Linda

22459, 15/12/2017, Numerov

22459, 12/12/2017, Joy.Gracy

22461, 14/12/2017, Tina#Deva

];

shekhar_analyti · ‎2017-12-20

Is there way to achieve above by variable . What i mean to say , i will store all possible delimiter in a variable and my script should scan through each delimiter from variable in loop and create output table .

sunny_talwar · ‎2017-12-20

You can store it and then your script can pull them into script and create a replace statement or you can maintain it in the script... either ways you need a replace statement and you need the list.... its up to your discretion how and where you want to store this

shekhar_analyti · ‎2017-12-20

Actually i was asking for solution around it ...

Many Thanks again .

maxgro · ‎2017-12-20

yes

but when you add a delimiter you have to add the new delimiter in some place;

the place could be the .qvw or an include or an external (txt, csv) file

To make it dynamic you can try this:

- use the purgechar function on the Person field to remove all the a b c d ....

- with the remaining characters (distinct) build a mapping table as in my previous post

sunny_talwar · ‎2017-12-20

Around what? Around having to maintain a list? I mean how do you expect QlikView to know what all is possible... you need to give something (a bare minimum) for it to know what the list of delimiter can be

shekhar_analyti · ‎2017-12-20

I am sorry , i guess i did not make myself clear enough .

I meant to tell that , it will be easier if i pass list of all possible delimiters in a variable that hard coding at few places .

something like this ...

vDelimiters= '*','&','!','@' ... and so on . But what if this itself is delimiter -> ' (single quote) .

sunny_talwar · ‎2017-12-20

You can do that too... just a little more work upfront... but is doable... and single quote is = Chr(39)... and I did add that to the Replace variable.

sunny_talwar · ‎2017-12-20

Something like this

LET vDelimiters = '~,!,@,#,$,%,^,&,*,(,),_,+,-,=,Chr(39),;,",:,},{,],[,\,|,?,>,<,.,/, ';

SET vChange = Replace(Replace(Replace(Replace(

Replace(Replace(Replace(Replace(

Replace(Replace(Replace(

$1,

SubField('$(vDelimiters)', ',', 1), ','),

SubField('$(vDelimiters)', ',', 2), ','),

SubField('$(vDelimiters)', ',', 3), ','),

SubField('$(vDelimiters)', ',', 4), ','),

SubField('$(vDelimiters)', ',', 5), ','),

SubField('$(vDelimiters)', ',', 6), ','),

SubField('$(vDelimiters)', ',', 7), ','),

SubField('$(vDelimiters)', ',', 8), ','),

SubField('$(vDelimiters)', ',', 9), ','),

SubField('$(vDelimiters)', ',', 10), ','),

SubField('$(vDelimiters)', ',', 11), ','),

SubField('$(vDelimiters)', ',', 12), ','),

SubField('$(vDelimiters)', ',', 13), ','),

SubField('$(vDelimiters)', ',', 14), ','),

SubField('$(vDelimiters)', ',', 15), ','),

SubField('$(vDelimiters)', ',', 16), ','),

SubField('$(vDelimiters)', ',', 17), ','),

SubField('$(vDelimiters)', ',', 18), ','),

SubField('$(vDelimiters)', ',', 19), ','),

SubField('$(vDelimiters)', ',', 20), ','),

SubField('$(vDelimiters)', ',', 21), ','),

SubField('$(vDelimiters)', ',', 22), ','),

SubField('$(vDelimiters)', ',', 23), ','),

SubField('$(vDelimiters)', ',', 24), ','),

SubField('$(vDelimiters)', ',', 25), ','),

SubField('$(vDelimiters)', ',', 26), ','),

SubField('$(vDelimiters)', ',', 27), ','),

SubField('$(vDelimiters)', ',', 28), ','),

SubField('$(vDelimiters)', ',', 29), ','),

SubField('$(vDelimiters)', ',', 30), ','),

SubField('$(vDelimiters)', ',', 31), ',');