3 Replies Latest reply: Aug 16, 2012 7:52 AM by Jason Michaelides RSS

    LOAD multiple where

      Hi,

       

      I load a bunch of qvd's, and I want to restrict some data :

       

      Source_table:

      LOAD *,

      MakeDate(Left(Fiscal_Period,4),Right(Fiscal_Period,3),) as Date

      From Report_*.qvd(qvd)

      WHERE ([Business segment]='DOWNSTREAM' OR [Business segment]='UPSTREAM'

      OR [Business unit] <> 'TRADE' OR [Business unit] <> 'OTHER DOWNSTREAM'

      OR [Business unit] <> 'PHOSPHATES' OR [Business unit] <> 'SPECIALITY JVS');

       

      That doesn't really do the trick, because I still see the unwanted data...

       

      are <> wrong tags?

        • Re: LOAD multiple where
          Jason Michaelides

          Looks OK to me.  Maybe try it on a single QVD file first?  And try removing the brackets around the group of OR statements - I don't think they're needed.

           

          Hope this helps,

           

          Jason

            • Re: LOAD multiple where

              yea Jason I figured it out by myself, it's just a logical thinking issue.

               

              first you need to load what you do want to see with an OR

              and with that selection in mind, you need to eliminate the things you don't want to see with AND's.

               

              otherwise you don't get the right results.

               

              This works, but increases loading time heavily.

               

              WHERE ([Business segment]='DOWNSTREAM' OR [Business segment]='UPSTREAM')

              AND [Business unit] <> 'TRADE' AND[Business unit] <> 'OTHER DOWNSTREAM'

              AND[Business unit] <> 'PHOSPHATES' AND [Business unit] <> 'SPECIALITY JVS');

               

              maybe it would be better to load everything at once, and then make the selections you need from the resident table and drop the source table.

                • Re: LOAD multiple where
                  Jason Michaelides

                  If it wasn't for the new field you are creating (Date) you could use Exists() instead of WHERE and retain an optimized load.  Might be best to do this anyway - you can test what's faster:

                   

                  1. Load the above using Exists(), with the Date

                  2. Load the above using Exists(), without the Date, then joining the Date afterwards.

                   

                  Let me know if you need help with the script from here.

                   

                  Jason