topic Re: Load Data by resident with limit number of records for each loaded id in QlikView

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

Anonymous — Wed, 04 Jan 2012 10:33:53 GMT

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

Wed, 04 Jan 2012 10:57:52 GMT

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 — Wed, 04 Jan 2012 11:01:38 GMT

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

Anonymous — Wed, 04 Jan 2012 13:26:51 GMT

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;

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

Wed, 04 Jan 2012 13:31:11 GMT

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.

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

Miguel_Angel_Baeyens — Wed, 04 Jan 2012 15:51:24 GMT

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