Comma Values in column

[#1]

HI all,

I have a situation in which Applymap and simple widmatch is not working.I have a column which contains following data

col1          col2

1               Duplicate

2               required filed missing:abcd,Duplicate

3               required filed missing:abcd,pqrs,Duplicate           

4               required filed missing:xyz

5               Member Not found

Now in filter I want to show:

Duplicate

Required Field Missing

Member Not Found

Now On selecting :

Duplicate:: records 1,2,3 show.

Required Field Missing:: records 2,3,4 show

Member Not Found:: record 5 show.

I search lot about this,but not able achieve this.Please help regarding this.

[maxgro · Accepted solution]

Tab:

LOAD * INLINE [

col1      ,    col2

1       ,        "Duplicate"

2       ,        "required filed missing:abcd,Duplicate"

3       ,        "required filed missing:abcd,pqrs,Duplicate"         

4       ,        "required filed missing:xyz"

5       ,        "Member Not found"

];

Tab2:

LOAD col1, 'Required' as col3 Resident Tab where WildMatch(col2, '*required*');

LOAD col1, 'Duplicate' as col3 Resident Tab where WildMatch(col2, '*dupli*');

LOAD col1, 'Not Found' as col3 Resident Tab where WildMatch(col2, '*member not found*');

col3 is your listobox

[martinpohl]

use this:

Orig:

LOAD * INLINE [

col1      ,    col2

1 ,"Duplicate"

2 ,"required filed missing:abcd,Duplicate"

3 ,"required filed missing:abcd,pqrs,Duplicate"        

4 ,"required filed missing:xyz"

5 ,"Member Not found"

];

Data:

load

col1,

col2 as col2_orig,

left(col2,if(index(col2,':')= 0 ,len(col2),(index(col2,':'))-1)) as col2

resident Orig;

drop table Orig;

[#4]

Use subfield(col2, ',') first in a temp table => it will duplicate the lines (1 per field)

you will get 3 lines where col1 = 2

col1   col2

2     required field missing

2    abcd

2    Duplicate

After that you load into a new table with a wildmatch() function

where wildmatch(col2, 'required field match', 'Duplicate', 'member not found')

Fabrice

[#5]

Hey thanks for replying.But this code is not working.it is giving correct for Duplicates but on select of required filed ,it only gives those records which contains required field and not filtered out in duplicate filter.

[#6]

Thank you Massimo.It perfectly fine for my scenario.

[#7]

Thank you martin for reply.But this code doesnt not meet my requirement.It filters out all the values in col2 but I need only 3 category for filter,as I mentioned.

[maxgro]

if the answer was helpful or correct please marks it; thanks and regards

[#9]

How I can mark it?

[maxgro]

There should be a button with mark as helpful or mark as correct for every answer or in the thread