4 Replies Latest reply: Sep 22, 2013 4:07 PM by Rohit Koul RSS

    Load Subset of data by One field Values

    Faisal Mehmood

       

      LOAD * INLINE [
         Division, Country, Period, Sales
            LA,       Mexico, 201009, 900
            LA,       Mexico, 201010, 1000]
      ;

      Concatenate
      Old_Data:
      LOAD Division,
          
      Country,
          
      Period,
         
      // Period as Alias,
           Sales
      FROM
      Current_Data.qvd
      (
      qvd)
      Where not Exists (Alias,Period);
      /*
      Current_Data.qvd contains data below but i only want subset of data . only those rows where Period value does not exist in above New_Data
      So i would not want row with period 201009 becasue it exist in above table.. Please advise
      Division Country Period Sales
      LA Mexico 201007 100
      LA Mexico 201008 400
      LA Mexico 201009 800
      */


       

        • Re: Load Subset of data by One field Values
          Stefan Wühl

          Not sure if I completely understood your request, you want to check, if the combination of all fields (except Sales values), can be found in the inline table?

           

          Then try creating an appropriate key:

           

          NEW:

          LOAD *, AutoNumberHash128(Division,Country,Period) as Key INLINE [

             Division, Country, Period, Sales

                LA,       Mexico, 201009, 900

                LA,       Mexico, 201010, 1000

          ];

           

           

          Concatenate

          Old_Data:

          LOAD Division,

               Country,

               Period,

               Sales

          INLINE [

          Division, Country, Period, Sales

          LA, Mexico, 201007, 100

          LA, Mexico, 201008, 400

          LA, Mexico, 201009, 800

          ]

          Where not Exists (Key,AutonumberHash128(Division,Country,Period));

           

           

          drop field Key;

          • Re: Load Subset of data by One field Values
            Faisal Mehmood

            Sorry I commented Alias field.. but inshort i want to load only those rows which are not there in first table by Period.

            • Re: Load Subset of data by One field Values
              Rohit Koul

              I hope I understood you requirement else you attach a Output

               

               

              find this :

               

              Historic_Data:

              LOAD * INLINE [

                 Division, Country, Period, Sales

                    LA,       Mexico, 201009, 900

                    LA,       Mexico, 201010, 1000];

                   

              New_Data:

              LOAD * INLINE [

                 Division, Country, Period, Sales

                 LA, Mexico, 201007, 100

                 LA, Mexico, 201008, 400

                 LA, Mexico, 201009, 800 

                    ] Where not Exists (Period,Period);

               

              your output will be :**************************************************

               

              DivisionCountryPeriodSales
              LAMexico201007100
              LAMexico201008400
              LAMexico201009900
              LAMexico2010101000
              • Re: Load Subset of data by One field Values
                Dirk Jonker

                Just use

                Where Not Exists (Period);