exclude records from list

[amien]

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?

[#2 · Accepted solution]

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,

[amien]

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) ..

[#4]

Hi

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?

[amien]

Thanks