Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
shekhar_analyti
Specialist
Specialist

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

Hi All ,

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

Capture.PNG

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

Thanks & Regards

Shekar

1 Solution

Accepted Solutions
sunny_talwar

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


SET vChange = Replace(Replace(Replace(Replace(

  Replace(Replace(Replace(Replace(

  Replace(Replace(Replace(Replace(

  Replace(Replace(Replace(Replace(

  Replace(Replace(Replace(Replace(

  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

    22461, 14/12/2017, Tina#Deva

];

View solution in original post

31 Replies
devarasu07
Master II
Master II

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);

Capture.JPG

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
Specialist
Specialist

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
Master II
Master II

Hi Shekhar,

Try like this:

load Doornbr,

Date,

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

from excel

Br,

KC

Best Regards,
KC
shekhar_analyti
Specialist
Specialist
Author

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
Specialist
Specialist
Author

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
Specialist
Specialist

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
Specialist
Specialist
Author

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

Yes above all are my possible delimiters .

mato32188
Specialist
Specialist

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
Specialist
Specialist
Author

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