7 Replies Latest reply: Nov 2, 2016 9:10 AM by rgv rand RSS

    Keep track of historical data

    rgv rand

      Hi

      I want to keep track of historical data which mean i would like to show how many days does particular is open.

      lets have a look at sample data

       

       

      TestData1:

             IDDesc
      1AAA
      2BBB
      3CCC
      4DDD

       

      TestData2:

             IDDesc
      1AAA
      2BBB
      3CCC
      6FFF

       

      Now I need to check TestData2 with TestData and if any match is found between two tables it should return 1 else 0.

      Likewise I need to append the Testdata2 to TestData1.

      After appending TestData1 consists of TestData2 as well.

      As it is daily reload it has check for match every day.Let's  say ID 1 is already existing TestData1 and TestData2 so it will return 1 .Now for the next time reload same ID 1 is present in the file it should get incremented by 1 .

       

      i have tried below script somehow i tried matching records but i struck with incrementing the matched records.

       

      TestData1:

      laod ID,Desc

      from TestData1.xls;

       

      load *,

      if(exists(ID),'1','0') as Idmatch

      from TestData1.xls

       

       

       

      Any Ideas !

       

      Thanks

        • Re: Keep track of historical data
          MARCO HADIYANTO

          Hi,

          I think your case looks like incremental load.

          you can find documents about incremental load and script in this link.

          Incremental LoadIncremental Load

          Incremental load for beginner with example

          Regards,

          Marco

          • Re: Keep track of historical data
            Gysbert Wassenaar

            Perhaps like this:

             

            TestData1:

            LOAD

                 ID,

                 ID as Idmatch1,

                 Desc,

                 1 as Counter

            FROM

                 TestData1.xls (biff, embedded labels, table is $Sheet1);

             

            IF FileTime('HistoricalData.qvd')>0 THEN

             

                 HistoricalData:

                 LOAD

                      ID,

                      ID as Idmatch2,

                      Desc,

                      RangeSum(If(Exists(Idmatch1, ID),1,0),Counter) as Counter

                 FROM

                      HistoricalData.qvd (qvd)

                      ;

             

                 DROP Field Idmatch1;

             

                 CONCATENATE (HistoricalData)

             

                 LOAD

                      *

                 RESIDENT

                      TestData1

                 WHERE

                      Not Exists(Idmatch2, ID)

                      ;

             

                 DROP Field Idmatch2;

                 STORE HistoricalData iNTO HistoricalData.qvd;

                 DROP TABLE TestData1;

             

            ELSE

             

                 DROP Field Idmatch1;

                 STORE TestData1 INTO HistoricalData.qvd;

             

            ENDIF

              • Re: Keep track of historical data
                rgv rand

                I am getting error Counter not found in HistoricalData table .

                  • Re: Keep track of historical data
                    Gysbert Wassenaar

                    Sorry, no idea. Works fine here.

                      • Re: Keep track of historical data
                        rgv rand

                        TestData1:

                        LOAD

                             ID,

                             ID as Idmatch1,

                             Desc,

                             1 as Counter

                        FROM

                            [C:\Users\Desktop\Sample.txt]

                        (txt, codepage is 1252, embedded labels, delimiter is ',', msq);

                         

                        IF FileTime('C:\Users\Desktop\HistoricalData.qvd')>0 THEN

                         

                             HistoricalData:

                             LOAD

                                  ID,

                                  ID as Idmatch2,

                                  Desc,

                                  RangeSum(If(Exists(Idmatch1, ID),1,0),Counter) as Counter

                             FROM

                                  C:\Users\Desktop\HistoricalData.qvd (qvd)

                                  ;

                         

                             DROP Field Idmatch1;

                         

                             CONCATENATE (HistoricalData)

                         

                             LOAD

                                  *

                             RESIDENT

                                  TestData1

                             WHERE

                                  Not Exists(Idmatch2, ID)

                                  ;

                         

                             DROP Field Idmatch2;

                             STORE HistoricalData INTO C:\Users\Desktop\HistoricalData.qvd;

                             DROP TABLE TestData1;

                         

                        ELSE

                         

                             DROP Field Idmatch1;

                             STORE TestData1 INTO C:\Users\Desktop\HistoricalData.qvd;

                         

                        ENDIF

                         

                         

                         

                        I am Using above script but its giving error

                         

                        Counter field not Found in Historical data

                         

                        Thanks

                  • Re: Keep track of historical data
                    rgv rand

                    Hi

                     

                    Sample Data is

                    ID,Desc

                    1,Apple

                    2,Orange

                    3,Pineapple

                    1,Apple

                     

                     

                    Thanks