14 Replies Latest reply: Sep 13, 2013 8:12 AM by Nayan Lalla RSS

    Excluding fields from a table

    Nayan Lalla

      Hi

       

      I want to exclude certain values in a table.

       

      For eg , In the Category table below , I want to excluded PRICE , LOA & REPORT GROUP .  How do I do the scripting ?

       

      Category
      TV & VIDEO
      TORCHES
      AUDIO OBSOLETE
      LOA
      APPLIANCES
      BATTERIES
      BRACKETS
      CABLES
      CABLE MANAGEMENT
      CCTV
      CLEANING KITS
      CABLE SOLUTIONS OBSOLETE
      COMPUTER NETWORKING
      DISPLAY STANDS
      ELLIES OBSOLETE
      ELECTRICAL
      ENERGY MONITORING
      ELECTRICAL OBSOLETE
      GENERATORS
      PRICE
      HDMI
      HEADPHONES
      REPORT GROUP
      HOME SECURITY
      INDUSTRIAL AUDIO
      INVERTERS
      LIGHTING
      MULTIMEDIA
      NETWORKING OBSOLETE
      OUTDOOR OBSOLETE
      PACKAGING
      PORTABLE DEVICES
      RAW MATERIALS ENGINEERING
      REMOTE WORLD
      SANWARE
      SATELLITE
      SHELVING
      SIGNAL DISTRIBUTION
      SATTELITE OBSOLETE
      SOLAR
      TELEPHONE
      WIND POWER
        • Re: Excluding fields from a table
          Lucas Vallinoto de Moraes

          Nayan,

           

          If you want to do this on the Script, you could use the inner join:

           

          //Example

          // To keep only Category

          // TV & VIDEO

          // TORCHES

          // Use

          inner Join ( FullTab )
          SelectedValues:
          LOAD * INLINE [
          Category
          TV & VIDEO
          TORCHES
          ]

           

           

          Hope I Could Help

          • Re: Excluding fields from a table
            mayilvahanan ramasamy

            Hi

             

            Try like this

             

            Load If(not wildmatch(Category, 'PRICE' , 'LOA','REPORT GROUP'), Category) as Category from tablename;

             

            Hope that helps

            • Re: Excluding fields from a table
              Carlos Reyes

              It's just the same as Mayil suggested but in the case you want to load more fields than Category from the same table, you should move that condition to the WHERE clause:

               

              LOAD

                        Category,

                        Field2,

                        Field3

              FROM TableName

              WHERE NOT MATCH (Category, 'PRICE' , 'LOA','REPORT GROUP')

              ;

                • Re: Excluding fields from a table
                  Nayan Lalla

                  Hi

                   

                  The formula dont seem to work.  What i have done is create an extract of my database in excel.

                  Below  are 5 items with main category and sub category (I've also attached an excel file)

                   

                  Also attached is a list of main Categories and Sub-Categories.

                   

                  In the table below i dont want to lines if the  Main Category Code = PRICE, REPORTGRP or LOA .  For each item there should be only one line.

                   

                  Just to let you know, on our database for Main Category Code equal to:

                  • PRICE  - the Sub Category Code is a any value    (see excel file attached)
                  • REPORTGRP - the Sub Category code B/O, WPU, wpu , WPY and also blank
                  • LOA - the Sub Category code is blank.

                   

                  Your help would be appreciated.

                   

                  kind regards

                  Nayan


                  Item and Categories.jpg

                  • Re: Re: Excluding fields from a table
                    Nayan Lalla

                    Hi

                     

                    The formula dont seem to work.  What I have done is created an extract of my database in excel.

                    Below  are 5 items with main category and sub category (I've also attached an excel file)

                     

                    Also attached is a list of main Categories and Sub-Categories.

                     

                    In the table below i dont want to lines if the  Main Category Code = PRICE, REPORTGRP or LOA .  How do i do the scripting. For each item there should be only one line.

                     

                    Just to let you know, on our database for Main Category Code equal to:

                    • PRICE  - the Sub Category Code is a any value    (see excel file attached)
                    • REPORTGRP - the Sub Category code B/O, WPU, wpu , WPY and also blank
                    • LOA - the Sub Category code is blank.

                     

                    Your help would be appreciated.

                     

                    kind regards

                    Nayan


                    Item and Categories.jpg

                      • Re: Re: Excluding fields from a table
                        bobbyraj santhiogu

                        Hi,
                        You can do that like this:

                         

                        LOAD
                        // All your other fields,
                             [Main Category Code]
                        FROM
                             // your file
                        Where
                        [Main Category Code]<>'PRICE'
                        AND
                        [Main Category Code]<>'LOA'
                        AND
                        [Main Category Code]<>'REPORTGRP'
                        ;

                          • Re: Excluding fields from a table
                            Nayan Lalla

                            Hi Bobbyraj

                             

                             

                             

                            Below is the actual scripting for the model im using. Where do I insert your scripting. 

                             

                             

                             

                            Regards

                             

                            Nayan

                             

                             

                             

                             

                             

                            ICITEMO:

                            LOAD

                            ITEMNO as ,

                            OPTFIELD  as ,

                                VALUE  as ;

                                SQL SELECT *

                                 from ICITEMO;

                             

                             

                            OptfldDetail:

                            LOAD OPTFIELD  as ,

                                 FDESC as ;

                            SQL SELECT *

                            FROM CSOPTFH;

                             

                            left join(OptfldDetail)

                            LOAD OPTFIELD as ,

                                 VALUE  as ,

                            //    AUDTDATE,

                            //    AUDTTIME,

                            //    AUDTUSER,

                            //    AUDTORG,

                            //    SORTEDVAL,

                                VDESC as ;

                            //    TYPE,

                            //    LENGTH,

                            //    DECIMALS,

                            //    ALLOWNULL,

                            //    VALIDATE;

                             

                            SQL SELECT *

                            FROM CSOPTFD;

                             

                             

                            left join (ICITEMO)

                            Load * resident OptfldDetail;

                            drop table OptfldDetail;

                              • Re: Excluding fields from a table
                                bobbyraj santhiogu

                                Hi Nayan,

                                 

                                Sorry but I don't understand your script...

                                You don't give any name after your 'as' so your script can't work!

                                 

                                Otherwise for your question, you have to insert the condition:

                                 

                                Where

                                Category<>'PRICE'

                                AND

                                Category<>'LOA'

                                AND

                                Category<>'REPORT GROUP'

                                ;

                                 

                                After each table where you are getting the field Category from.

                                 

                                I'm afraid you can't insert the condition Where after SQL SELECT, you have to check that.

                                But in that case, you just have to load everything in a temporary table with SQL SELECT, then to load a new table with the condition from your temporary table then drop it.

                                 

                                If you can send a copy of your qvw it will be easyer to help you

                        • Re: Excluding fields from a table
                          bobbyraj santhiogu

                          Hi,

                          You can do that like this:

                           

                           

                          LOAD

                           

                               Category
                          FROM

                               XXX

                          Where

                          Category<>'PRICE'

                          AND

                          Category<>'LOA'

                          AND

                          Category<>'REPORT GROUP'

                          ;

                          • Re: Excluding fields from a table
                            Nayan Lalla

                            Hi All

                             

                            Thanks for all the responses.  The actual model i have a a bit more complicated.  I'll try the formulae given and give feedback.

                             

                            kind regards

                            Nayan