topic exclude records from list in QlikView

exclude records from list

amien — Fri, 19 Mar 2010 07:44:28 GMT


LOAD *
FROM [1.CSV] (ansi, txt, delimiter is ';', no labels, msq)
WHERE @6 <> 'X1' AND @6 <> 'X2' AND @6 <> 'X3'
;

i need to add a lot of @6 values .. can i put them in a inline .. and then excluded them? how can i do this?

exclude records from list

Fri, 19 Mar 2010 08:14:47 GMT

Hello Amien

You can do this by creating an inline table of @6 values that should be included, or @6 values that should not be included,


ExcludeValues:
LOAD * INLINE
[ @6Exclude
X1
X2
X3];
YourTableName:
LOAD *
FROM [1.CSV] (ansi, txt, delimiter is ';', no labels, msq)
WHERE NOT Exists(@6Exclude, @6);

You could also do it the other way around by creating an inline table of values to INCLUDE, and the only change there would be that you should remove the word NOT from the WHERE clause in your final load statement.

Good luck,

exclude records from list

amien — Fri, 19 Mar 2010 13:26:10 GMT

great solution .. works..

is it also possible to use this one in an expression? 'exists' doenst exists as function.

and how can i not get the found value as return .. but another value from the inline?

<pre>
[ @6Exclude, @6Exclude2X1, Test
X2, Test
X3, Test];

so .. the expression shouldnt return true or false .. but the value Test..

(mind that these are two different question) ..

exclude records from list

Fri, 19 Mar 2010 14:15:31 GMT

For the expression I would use INDEX which is similar to FINDSTR, CHARINDEX etc, something like:

Index(GetFieldSelections("ColName"),'Dim1')

The above expression will give you a return number > 0 if Dim1 has been selected in the field ColName, equally, you could put anything else instead of GetFieldSelections("ColName") in this expression.

I'm afraid I don't understand you second question, where are you wanting to show the value "Test" is this in the load script (and therefore into a table) or is it in a chart?

exclude records from list

amien — Fri, 19 Mar 2010 19:06:53 GMT

Thanks