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

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

shekhar_analyti — Wed, 25 Nov 2020 16:16:04 GMT

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

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

devarasu07 — Wed, 20 Dec 2017 05:32:28 GMT

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

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

luismadriz — Wed, 20 Dec 2017 05:33:15 GMT

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

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

jyothish8807 — Wed, 20 Dec 2017 05:33:34 GMT

Hi Shekhar,

Try like this:

load Doornbr,

Date,

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

from excel

Br,

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

shekhar_analyti — Wed, 20 Dec 2017 06:47:37 GMT

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

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

shekhar_analyti — Wed, 20 Dec 2017 06:52:04 GMT

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 .

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

luismadriz — Wed, 20 Dec 2017 07:05:31 GMT

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

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

shekhar_analyti — Wed, 20 Dec 2017 07:12:01 GMT

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

Yes above all are my possible delimiters .

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

mato32188 — Wed, 20 Dec 2017 07:37:13 GMT

Hi Shekar,

try to use this:

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

Thanks.

Martin

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

shekhar_analyti — Wed, 20 Dec 2017 07:43:43 GMT

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

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

luismadriz — Wed, 20 Dec 2017 07:45:02 GMT

Good, is not that long of a list, you can then apply Deva's or KC's solutions. You're just going to have a long sentence

The only thing you need to remember is to add Distinct to the load to remove duplicates

I hope this helps,

Cheers,

Luis

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

mato32188 — Wed, 20 Dec 2017 07:46:24 GMT

Have you tried that? Try to apply this logic in code above that have been provided by Devarasu R.

Martin

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

maxgro — Wed, 20 Dec 2017 08:21:17 GMT

you can use the mapsubstring to replace the delimiters

and then the sublfield

map2:

mapping

LOAD *

Inline [

From, To

~, @

!, @

@, @

#, @

., @

' ',@

',',@

-,@

] ;

t1:

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"

22461,14/12/2017,"aaa bbb"

1,15/12/2017,"ccc-ddd"

];

t2:

Productmodels:

LOAD

Subfield(MapSubString('map2', Person), '@') as Person2

Resident t1;

DROP Table t1;

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

luismadriz — Wed, 20 Dec 2017 08:23:02 GMT

Hi,

Below is what I was going to propose but it's not working properly and I'm not sure why. The order in which I create the string vDelimiters affects the results.

If I use vDelimiters = '.#,'; Then I get the 13 records as requested

but if I change it to vDelimiters = ',.#'; then I get 12 records

I may be missing something!!! Maybe someone else could troubleshoot because I need to go home now

vDelimiters = '.#,';

For i = 1 to 3

vChar = MID(vDelimiters,i,1);

Load Distinct Doornbr,

Date,

SubField(Person,'$(vChar)') as Person;

Next;

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"];

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

shekhar_analyti — Wed, 20 Dec 2017 09:12:16 GMT

Thanks Massimo . Please attach sample .

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

shekhar_analyti — Wed, 20 Dec 2017 09:16:40 GMT

Is this not a dependency ? Everytime i have to update this , if a new delimiter comes in picture

map2:

mapping

LOAD *

Inline [

From, To

~, @

!, @

@, @

#, @

., @

' ',@

',',@

-,@

] ;

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

shekhar_analyti — Wed, 20 Dec 2017 12:20:20 GMT

Please Help to make it dynamic .. stalwar1‌

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

sunny_talwar — Wed, 20 Dec 2017 12:32:36 GMT

What exactly do you mean when you say to make this dynamic?

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

shekhar_analyti — Wed, 20 Dec 2017 12:43:06 GMT

Thank you Sunny the for reply .

Dynamic means ... irrespective of type of delimiter (which we have discussed) . script should handle it and generate output table as expected .

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

shekhar_analyti — Wed, 20 Dec 2017 13:00:58 GMT

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