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

[Anonymous]

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

[#2]

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...

[Miguel_Angel_Baeyens · Accepted solution]

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

[Anonymous]

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;

[#5]

Regarding Date (if someone need it):

I changed: Date('01/01/2012' + Ceil(Rand() * 365)) AS Date,

to:

Date('2012-01-01' + Ceil(Rand() * 365)) AS Date,

that is my local setting for date format.

[Miguel_Angel_Baeyens]

Hi,

I corrected the code above so anyone else can use it.

In regards to the date, Thomas points to the right answer: regional settings differ from country to country. The working date format is always easy to know as to go to the Script Editor, and look ad the values DateFormat and TimestampFormat located at the top of the main script tab (the first on the left, in case you have more than one).

Glad to help.

Miguel