Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
mila_ayuhara
New Contributor III

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

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

Tags (1)
1 Solution

Accepted Solutions

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

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

5 Replies
Not applicable

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

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

mila_ayuhara
New Contributor III

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

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;

Not applicable

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

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

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

Community Browser