topic Re: NOT like in QlikView

NOT like

Mon, 03 Nov 2014 10:27:28 GMT

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

Re: NOT like

Colin-Albert — Mon, 03 Nov 2014 10:33:41 GMT

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.

Re: NOT like

alexandros17 — Mon, 03 Nov 2014 10:33:43 GMT

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

Re: NOT like

its_anandrjs — Mon, 03 Nov 2014 10:34:19 GMT

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

Re: NOT like

Mon, 03 Nov 2014 11:12:14 GMT

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.

Re: NOT like

VishalWaghole — Mon, 03 Nov 2014 11:24:49 GMT

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

Re: NOT like

danieloberbilli — Mon, 03 Nov 2014 11:29:57 GMT

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

Re: NOT like

Mon, 03 Nov 2014 11:35:25 GMT

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