7 Replies Latest reply: Nov 3, 2014 6:35 AM by Usman Sikander RSS

    NOT like

      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

          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
            Alessandro Saccone

            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
              Anand Chouhan

              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

                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
                    Daniel Oberbillig

                    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
                    vishal waghole

                    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