Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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.
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
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
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.
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
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()
Thank you Vishal. It worked. I was using the same statement but with other format like Where not wildmatch(ABC,'*XYZ*') anyways thanks again.