5 Replies Latest reply: Jan 4, 2012 10:51 AM by Miguel Angel Baeyens de Arce RSS

    Load Data by resident with limit number of records for each loaded id

    Franziska Schütte

      Hello,

       

      i want to load a limited number (e.g. 1000) of the last timestamps for each loaded id. I load the data by a resident and sort it

       

      load id,

             timestamp

      resident Data

      order by id, timestamp desc;

       

      At that point, i dont know how to go on. I want to have the datarecords of the last 1000 timestamps for each id. How can i solve that problem?

       

       

      Best greetings.

      Mila

        • Re: Load Data by resident with limit number of records for each loaded id

          Well, you need two variables that keep track of 1) id and 2) rownr, then do a nested loop with for...next. That is if your ID is numeric...

          • Re: Load Data by resident with limit number of records for each loaded id
            Miguel Angel Baeyens de Arce

            Hi Mila,

             

            Consider the following piece of script:

             

            DataTemp:
            LOAD Chr(64 + Ceil(Rand() * 3)) AS ID,
                 Date('01/01/2012' + Ceil(Rand() * 365)) AS Date,
                 Ceil(Rand() * 1000) AS Amount
            AUTOGENERATE 100;
            
            AllIDs:
            LOAD Chr(39) & Concat(DISTINCT ID, Chr(39) & Chr(44) & Chr(39)) & Chr(39) AS AllIDs
            RESIDENT DataTemp;
            
            LET vAllPossibleIDs = FieldValue('AllIDs', 1);
            
            DROP TABLE AllIDs;
            
            FOR EACH vID in $(vAllPossibleIDs)
            
                 Data:
                 FIRST 10 LOAD ID,
                      Date,
                      Amount,
                      RowNo() AS Dummy // to avoid concatenation with DataTemp
                                                      // but allow it with the following iteration
                 RESIDENT DataTemp
                 WHERE ID = '$(vID)'
                 ORDER BY Date DESC;
            
            NEXT 
            
            DROP TABLE DataTemp; 
            
            DROP FIELD Dummy;
            

             

            Comments:

            • DataTemp is your source table, according to your example above, table "Data"
            • I know I can easily do a FOR loop for each ID,
            • I know I can load the first N rows for any given table
            • and I know that if I sort the table by Date, descending, the first 10 records will return the 10 highest dates (not distinct, so the 10 dates might be the same value)
            • I want to concatenate the resulting table, but now with the source table, so I have to differentiate the number of fields, therefore I create a dummy field I can drop it later

             

            Hope that helps.

             

            Miguel

              • Re: Load Data by resident with limit number of records for each loaded id
                Franziska Schütte

                Thanks Miguel for that answer.

                 

                I could successfully make the code work for my problem.Your code works fine. There were only two things missing:

                1. one line missing: where ID = '$(vID)' in the for each load of the table Data.

                2. The created Date didnt work. I didnt correct it as i didnt need it.

                 

                Many thanks!

                Mila

                 

                Here is the corrected version:

                DataTemp:
                LOAD Chr(64 + Ceil(Rand() * 3)) AS ID,
                     Date('01/01/2012' + Ceil(Rand() * 365)) AS Date,
                     Ceil(Rand() * 1000) AS Amount
                AUTOGENERATE 100;

                AllIDs:
                LOAD Chr(39) & Concat(DISTINCT ID, Chr(39) & Chr(44) & Chr(39)) & Chr(39) AS AllIDs
                RESIDENT DataTemp;

                LET vAllPossibleIDs = FieldValue('AllIDs', 1);

                DROP TABLE AllIDs;

                FOR EACH vID in $(vAllPossibleIDs)

                     Data:
                     FIRST 10 LOAD ID,
                          Date,
                          Amount,
                          RowNo() AS Dummy // to avoid concatenation with DataTemp
                                                          // but allow it with the following iteration
                     RESIDENT DataTemp

                     where id = '$(vID)'
                     ORDER BY Date DESC;

                NEXT

                DROP TABLE DataTemp;

                DROP FIELD Dummy;