5 Replies Latest reply: Dec 3, 2015 12:43 AM by jagan mohan rao appala RSS

    Compare Records

    susvithrocks susvithrocks

      Hello all, gwassenaar, jagan

       

      I got a requirement which I never worked earlier. I have a total 4 QVW's which are PLD1000, PLD1100, PLD1200 and PLD1300.

      PLD1000 is the the parent qvw and rest are children. Due to heavy load of 45 million records in PLD1000, the records has been divided into PLD1100, PLD1200 and PLD1300 as per requirements. Which means sum of record count in PLD1100+PLD1200+PLD1300 = PLD1000.

       

      The problem here is, we observed that some unwanted records are also getting fetched in children qvw's. The sum of records is not validating. So the task is here, to compare the records in each qvw and if it is not matched the load should fail and should show the reason in the log file as " count of records not matched".

       

      The idea I am trying to execute is to insert the main table without any transformations in one of the child file with the respective business logic. And I am trying to write IF condition to validate the records and fail the load process if it is not matching and show it in log file. I got struck in writing this condition. Can anyone could please help me out? Any other ideas to achieve this are also appreciated.

       

      Lets assume main table to be inserted as

      Main_Data:

      A

      B

      C

       

      Now the number of records from this table should match with whole extracted records.

       

      PS: Sorry for a long post. I tried to be brief as much as possible.

       

      Thanks in Advance.

       

      BR

      Susvith

        • Re: Compare Records
          jagan mohan rao appala

          Hi,

          Get the rows count of the table by using NoOfRows() in the script, and compare if it is not matching then throw and error like below

           

          Data:

          LOAD

          *

          FROm TableName;

           

          If NoOfRows('TableName') <> SomeNumber THEN

            TRACE count of records not matched   // Trace logs the given text in log file.

            DROP TABLE Dummy;   // To raise an error we are dropping a table that doesn't exists

          END IF

           

          Hope this helps you.

           

          Regards,

          jagan.

          • Re: Compare Records
            susvithrocks susvithrocks

            Hi jagan

             

            Thanks for you reply.

             

            I tried working as you said above, but its giving some syntax error.

            58.png

             

            I managed to keep this post more easier at

            Condition Reload

            Can you please check it for me?

             

            Thanks in advance.

             

            BR
            Susvith

            • Re: Compare Records
              Jonathan Dienst

              Compare the NoOfRows('TableName') With QvdNoOfRecords('MyFile.qvd')...

                • Re: Compare Records
                  susvithrocks susvithrocks

                  Thanks for your reply.

                   

                  I had tried writing it.

                  But I got failed in writing the if condition reload.

                   

                  Can you please help me in it. For sample files Condition Reload

                   

                  Thanks in Advance

                   

                  Br

                  Susvith

                    • Re: Compare Records
                      jagan mohan rao appala

                      HI,

                       

                      Try like this

                       

                      Temp1:

                      LOAD

                           Sum(UnitCost + UnitPrice) as Sum1

                      FROM

                      Products.qvd

                      (qvd);

                       

                       

                      NoConcatenate

                      Test:

                      LOAD CategoryID,

                           Pricegroup1,

                           ProductID,

                           ProductName,

                           QuantityPerUnit,

                           SupplierID,

                           UnitCost,

                           UnitPrice,

                           UnitsInStock,

                           UnitsOnOrder

                      FROM

                      Nikhil.xls

                      (biff, embedded labels, table is Sheet1$);

                       

                       

                      Temp2:

                      LOAD

                           Sum(UnitCost + UnitPrice) AS Sum2   

                      FROM

                      Nikhil.xls

                      (biff, embedded labels, table is Sheet1$);

                       

                       

                       

                       

                       

                       

                      Let TableCount= NoOfRows('Test');

                      Let QvdCount= QvdNoOfRecords('Products.qvd');

                      Let vQVDSum= Peek('Sum1', 0, 'Temp1');

                      Let vExcelSum= Peek('Sum2', 0, 'Temp2');

                       

                       

                      IF TableCount <> QvdCount THEN

                        TRACE "Records count not matched";

                        DROP TABLE TEst007;

                      ELSEIF vQVDSum - vExcelSum > 0 THEN

                        TRACE Sum not matched;

                        DROP TABLE TEst007;

                      ENDIF

                       

                       

                      Drop Table Temp1, Temp2;

                       

                      Regards,

                      Jagan.