NOT like

[#1]

Hello Everybody,

I am facing a small problem in not like statement

I have one Column and in that column I dont want specific Row can anybody guide me in this regard.

Column Name: ABC

Row not wanted: XYZ

Thank you in advance

[Colin-Albert]

There are various ways to achieve this, can you be more specific on where you want to exclude the data- in the script, in a chart...

On the front-end, the simplest way is to add a list box for field ABC, select value XYZ, then right-click and select "Select Excluded" from the list of options.

[alexandros17]

If you are using SQL then

Select

...

From

...

Where not ABC like 'XYZ';

If you are using qlik load, you can use wildmatch function so:

Load

...

From

...

Where

Wildmatch(ABC, 'XYZ')=0;

let me know

[its_anandrjs]

Write like

Load

ABC

From Location

Where ABC <> 'XYZ'

I assume XYZ is the content in the fields ABC like this you avoid the row to be loaded in load script

If have any sample file then provide that.

Regards

Anand

[#5]

Thanks everybody. Every mentioned Soution is vaild and it works but the thing is the associated data from other columns are still there and altogether the number of rows are not reduced which eventually have to. But the solution from Mr.Colin Albert was effective and reduced the over all count as welll. Can I do something with the script and get the desired result. I have connected the excel file and Datawarehouse. ABC column is in Excel file.

[VishalWaghole · Accepted solution]

Hi Usman,

Please try this,

LOAD ABC,

     Column1,

     Column2,

     Column3

FROM

Book1.xlsx (ooxml, embedded labels, table is Sheet1)

Where not WildMatch(ABC,'XYZ')

Hope it will helpful for you.

-- Regards,

Vishal Waghole

[danieloberbilli]

if you script

LOAD

Field1,

Field2,

...

FROM ...

Where ABC <> 'XYZ';

make sure that the XYZ is exactly written as the value in your data row (case sensitive) and check if there are space characters before or after the cell content which you can avoid with trim()

[#8]

Thank you Vishal. It worked. I was using the same statement but with other format like Where not wildmatch(ABC,'*XYZ*') anyways thanks again.