topic Re: Comma Values in column in QlikView

Comma Values in column

Wed, 19 Feb 2014 12:24:32 GMT

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.

Re: Comma Values in column

maxgro — Wed, 19 Feb 2014 12:52:44 GMT

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

Re: Comma Values in column

martinpohl — Wed, 19 Feb 2014 13:02:55 GMT

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;

Re: Comma Values in column

Wed, 19 Feb 2014 13:26:36 GMT

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

Re: Comma Values in column

Wed, 19 Feb 2014 14:10:37 GMT

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.

Re: Comma Values in column

Wed, 19 Feb 2014 14:13:53 GMT

Thank you Massimo.It perfectly fine for my scenario.

Re: Comma Values in column

Wed, 19 Feb 2014 14:29:05 GMT

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.

Re: Comma Values in column

maxgro — Wed, 19 Feb 2014 14:39:30 GMT

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

Re: Comma Values in column

Wed, 19 Feb 2014 15:29:50 GMT

How I can mark it?

Re: Comma Values in column

maxgro — Wed, 19 Feb 2014 15:36:47 GMT

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

Re: Comma Values in column

Wed, 19 Feb 2014 15:47:05 GMT

Ok,I will mark it.@Mssiomo I have also posted a query regarding date range selection from calender objects.I have attached a test qvw now.Can you please see it and provide help if possible.

My query was::"Table Box filters records by selecting date range from two calender objects"