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

[shekhar_analyti]

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

[luismadriz]

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

[mato32188]

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

BR

Martin

[maxgro]

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;

[luismadriz]

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

[shekhar_analyti]

Thanks Massimo . Please attach sample .

[shekhar_analyti]

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

map2:

mapping

LOAD *

Inline [

From, To

~, @

!, @

@, @

#, @

., @

' ',@

',',@

-,@

] ;

[shekhar_analyti]

Please Help to make it dynamic .. stalwar1‌

[sunny_talwar]

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

[shekhar_analyti]

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 .

[shekhar_analyti]

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