11 Replies Latest reply: Jul 8, 2011 7:14 AM by Kaushik Solanki RSS

    Issue with incremental load using variable

      Hi

       

      I am trying to do incremental load with the following script. But gives the error message saying that "Field not found - <MaxYear>". As per me everything looks to be correct only. Can some please tell me whats wrong with this script.

       

      ExistingTable:
      LOAD Field1
           ,Field2
           ,Year
           Month
      ..\App\Existing.qvd (qvd);
      
      Temp:
      Load Max(Year) AS MaxYear,Max(Month) AS MaxMonth Resident ExistingTable;
      
      Concatenate(ExistingTable)
      
      LOAD Field1 
           ,Field2
           ,Year
           ,Month    
      FROM
      ..\App\Data.xls
      (ooxml, embedded labels, table is Sheet1)
      Where Year > $(vMaxYear)
      Where Month > $(vMaxMonth);
      
      

       

      Thanks

       

      Attitude

        • Re: Issue with incremental load using variable

          Hi

           

          I think I need to pass the variable like below. Am I right?

           

           

          Where Year > '$(vMaxYear)'
          Where Month > '$(vMaxMonth)';
          

           

          Thanks

           

          Attitude

            • Re: Issue with incremental load using variable

              Hi All

               

              Below is the change which I did. I hope this way incremental should be working fine.

               

              What I am trying to do here is I just want to append only those rows where the Year/Month > Existing(Max(Year)/Max(Month)). Please correct me if I am wrong any where here.

               

              ExistingTable:
              LOAD Field1
                   ,Field2
                   ,Year
                   Month
              ..\App\Existing.qvd (qvd);
              
              
              Temp:
              Load Max(Year) AS MaxYear,Max(Month) AS MaxMonth Resident ExistingTable;
              
              
              Concatenate(ExistingTable)
              
              
              LOAD Field1 
                   ,Field2
                   ,Year
                   ,Month    
              FROM
              ..\App\Data.xls
              (ooxml, embedded labels, table is Sheet1)
              Where Year > '$(vMaxYear)'
              Where Month > '$(vMaxMonth)';
              
              
              
              
                • Re: Issue with incremental load using variable
                  Kaushik Solanki

                  Hi,

                   

                     Try this.

                   

                     ExistingTable:
                  LOAD Field1
                       ,Field2
                       ,Year
                       Month
                  ..\App\Existing.qvd (qvd);


                  Temp:
                  Load Max(Year) AS MaxYear,Max(Month) AS MaxMonth Resident ExistingTable;

                  let MaxYear = peek('MaxYear',0,'Temp');

                  let MaxMonth = peek('MaxMonth',0,'Temp');


                  Concatenate(ExistingTable)


                  LOAD Field1
                       ,Field2
                       ,Year
                       ,Month   
                  FROM
                  ..\App\Data.xls
                  (ooxml, embedded labels, table is Sheet1)
                  Where Year > '$(vMaxYear)'
                  Where Month > '$(vMaxMonth)';

                   

                  Regards,

                  Kaushik Solanki

                    • Re: Issue with incremental load using variable

                      Hi Koushik

                       

                      Thanks for your help. I think there is one small mistake in the script given by you. I think you forgot to add v infront of the variable name. If it is like that only then please let me know.

                       

                      let vMaxYear = peek('MaxYear',0,'Temp');
                      let vMaxMonth = peek('MaxMonth',0,'Temp');
                      

                       

                      If the above changes are correct then I think I am passing the where condition('$(vMaxYear)' and '$(vMaxMonth)') is also correct only. If the above changes are incorrect then do I need to pass '$(MaxYear)' and '$(MaxMonth)' instead of ('$(vMaxYear)' and '$(vMaxMonth)').

                       

                      For your information, I have created the variable vMaxYear = Max(Year) and vMaxMonth=Max(Month).

                       

                      LOAD Field1 
                           ,Field2
                           ,Year
                           ,Month    
                      FROM
                      ..\App\Data.xls
                      (ooxml, embedded labels, table is Sheet1)
                      Where Year > '$(vMaxYear)'
                      Where Month > '$(vMaxMonth)'; 
                      


                      Please tell me the exact changes are that are required in the script please.

                       

                      Thanks

                       

                      Attitude