topic Re: Loop Through Date and Create QVD in QlikView

Loop Through Date and Create QVD

Thu, 30 Oct 2014 16:36:17 GMT

Hi everyone,

This is probably a simple concept but I am not all that familiar with loops. Basically I have the following in a table, say with a date from 01/01/2013 through today 10/30/2014.

Data:

Reported_Date, Value

01/01/2013, 1.5

...

06/30/2014, 1.2

...

10/30/2014, 1.8

I would like to loop through and create a QVD for each date from 06/30/2014 and forward. What's the best way to do this?

Thanks!

Re: Loop Through Date and Create QVD

alexandros17 — Thu, 30 Oct 2014 16:41:55 GMT

The logic must be something like:

For i = 1 to number of rows of the table (Look for NoofRows() function)

LET myDate=Peek(myDateField,$(i));

MYTAB:

noconcatenate

Select * from MyTable where myDateField = $(myDate);

Store * from MYTAB INTO mytab_$(myDate).qvd;

drop table MYTAB;

Let me know

Re: Loop Through Date and Create QVD

Thu, 30 Oct 2014 16:47:53 GMT

Hello Mark, Please find the below:

LOAD Reported_Date, Value From source_table :

LET vRows =

FOR i=1 to FieldValueCount('Reported_Date')

LET vDate = FieldValue( 'Reported_Date', $(i) );

QVDNAME_$(vDate):

LOAD * FROM ..(ur statement)

STORE QVDNAME_$(vDate) FROM QVDNAME_$(vDate).qvd (qvd) ;

DROP Table QVDNAME_$(vDate):

Re: Loop Through Date and Create QVD

Thu, 30 Oct 2014 17:24:24 GMT

Hi Alessandro - thanks for your response. I receive the following errors and am not sure why. It appears QV is having trouble understanding the variable $(vDate) in the where clause.

Field not found - <=>

MyTable:

NoConcatenate

LOAD * RESIDENT Data where [REPORTED_DATE]=

---------------------------------

For i = 1 to NoOfRows(Data)

LET vDate=Peek([REPORTED_DATE],$(i));

MyTable:

NoConcatenate

LOAD * RESIDENT Data where [REPORTED_DATE]=$(vDate);

Store MyTable into [DailyStats_$(vDate).qvd](qvd);

Drop Table MyTable;

Re: Loop Through Date and Create QVD

Anonymous — Thu, 30 Oct 2014 18:31:43 GMT

use peek function.

Re: Loop Through Date and Create QVD

alexandros17 — Thu, 30 Oct 2014 21:09:50 GMT

try with

For i = 1 to NoOfRows(MyTable)

verify (with a text box that NoOfRows(MyTable) returns the right number