Solved: How to convert a single row into multiple based on... - 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

];

View solution in original post

devarasu07 · ‎2017-12-20

Hi,

you can try like this?

Fact:
load *, Trim(SubField(SubField(SubField(Person, ','), '.'),'#')) as Person_Revised;
LOAD Doornbr,
     Date,
     Person
FROM

(ooxml, embedded labels, table is AreaA);
Concatenate
load *, Trim(SubField(SubField(SubField(Person, ','), '.'),'#')) as Person_Revised;
LOAD Doornbr,
     Date,
     Person
FROM

(ooxml, embedded labels, table is AreaB);

Hope this helps to you

Close this thread by Marking helpful and correct. if might useful for other who is having similar issue. Tks

Regards,

Deva

luismadriz · ‎2017-12-20

Hi,

Can you please try this:

Table:

LOAD DISTINCT

Doornbr,

Date,

SubField(SinglePerson2,'.') as SinglePerson;

LOAD

Doornbr,

Date,

SubField(SinglePerson1,'#') as SinglePerson2;

LOAD

Doornbr,

Date,

SubField(Person,',') as SinglePerson1;

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"

22461,14/12/2017,"Tina#Deva"];

Please let me know if it works,

Cheers,

Luis

jyothish8807 · ‎2017-12-20

Hi Shekhar,

Try like this:

load Doornbr,

Date,

subfield(replace(replace(replace(Person,',','|'),'#','|'),'.','|'),'|') as Person

from excel

Br,

KC

Best Regards,
KC

shekhar_analyti · ‎2017-12-20

Hi Dev ,

Thanks for reply . Your solution is working fine with above sample data . But the problem i am facing is that delimiters are not just comma , hash or dot . Actually it varies within other special characters like (* , & , ^ , @ etc ) .

So my requirement is to handle any of the delimiter dynamically .

Thanks & Regards

Shekar

shekhar_analyti · ‎2017-12-20

Thanks for reply Luis . But in real data field delimiters

are not just comma , hash or dot . Actually it varies within other special characters like (* , & , ^ , @ etc ) .

So my requirement is to handle any of the delimiter dynamically .

luismadriz · ‎2017-12-20

Hi,

Is it possible for you to list all possible delimiters in a string or characters?

Such as this for example: ~!@#$%^&*()_+-=';":}{][\|?><,./

This is important because for example, is a space or a dash or an apostrophe or a number a delimiter?

There could be a for-next loop that checks for all possible or not possible characters... but something needs to be provided

Cheers,

Luis

shekhar_analyti · ‎2017-12-20

.... Actually You have rightly mentioned . Apart from these ~!@#$%^&*()_+-=';":}{][\|?><,./ ,I noticed Space and hyphen as well .

Yes above all are my possible delimiters .

mato32188 · ‎2017-12-20

Hi Shekar,

try to use this:

subfield(Replace(Person,PurgeChar(Person,'abcdefghijklmnopqrstuvwxyz'),','),',') as YourNewPerson

Thanks.

BR

Martin

ECG line chart is the most important visualization in your life.

shekhar_analyti · ‎2017-12-20

I am not really sure if that works .Please attach sample .