Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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 DataTempWHERE ID = '$(vID)'
ORDER BY Date DESC;
NEXT
DROP TABLE DataTemp;DROP FIELD Dummy;
Comments:
Hope that helps.
Miguel
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...
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 DataTempWHERE ID = '$(vID)'
ORDER BY Date DESC;
NEXT
DROP TABLE DataTemp;DROP FIELD Dummy;
Comments:
Hope that helps.
Miguel
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;
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.
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