Scripting error qvds

[#1]

Hi

I have been struggling for a while with qvd's

I am receiving this error when running this code, what am I missing here.

I am using the Text function to try to change the data type as I was getting qvd's created for 201413, 201414 etc, I only want a file for every actual Period which runs from 2003 - 01 through 12, then 2014-01 to 12

Script line error:

FOR vPeriod =  to

Noconcatenate TempPeriod:

LOAD distinct Text(HPeriod) as Period

Resident HISTORYAGEING

order by HPeriod;

Noconcatenate TempMinMax:

LOAD FirstValue(HPeriod) as MinPeriod,

LastValue(HPeriod) as MaxPeriod

Resident HISTORYAGEING;

LET vPeriodMin = Peek(MinPeriod);

LET vPeriodMax = Peek(MaxPeriod);

FOR vPeriod = $(vPeriodMin) to $(vPeriodMax)

NoConcatenate

TempData:

LOAD * Resident HISTORYAGEING where HPeriod = $(vPeriod);

STORE TempData into HISTORYAGEING_$(vPeriod).qvd;

Drop table TempData;

NEXT

[#2]

There are too many records, I may consider this if I have nowhere else to go, I found the problem thought, not sure that the solution is, check my response to Michael

[#3]

Hi Michael

I know what the problem is:

I set all these variables in the load

LET vPeriodMin = Peek('Period',0,'TempPeriod');

LET vPeriodMax = Peek('Period',-1,'TempPeriod');

LET vPeriodMin1 = Peek('Period',1,'TempPeriod');

LET vPeriodMin2 = Peek('Period',2,'TempPeriod');

when showing the values in a text box I get the following values

201307, 201308, 201408, 201407

To me this says it has something to do with the load order

but my load says:

TempPeriod:

LOAD distinct HPeriod as Period

Resident HISTORYAGEING

order by HPeriod;

I can't understand why this would be like this. The obvious answer for me is get the load order right, I just have no clue how, the Load on TempPeriod seems right

[Anonymous]

I think something is missing in your info...  Maybe the variables are re-defined down the road(?)  Order always worked for me.  Anyway, try to return to min and max, so you don't depend on order:

TempPeriod:

LOAD distinct

      min(Period) as MinPeriod,

      max(Period) as MaxPeriod

Resident HISTORYAGEING;

LET vPeriodMin = Peek('MinPeriod');

LET vPeriodMax = Peek('MaxPeriod');

DROP TABLE TempPeriod;

...

[#5]

I am running this like you suggest, It now creates a file for 201313-201400

and then continues with 201401 through 201408, how do I get rid of the

months 201313 to 201400 files.Maybe a for each value would be more of use,

I just can't get that to work either. The number as stated above comes

through as an integer from the database

On Mon, May 11, 2015 at 2:06 PM, Michael Solomovich <

[Anonymous]

I think there is an easier way.

TempPeriod:

LOAD distinct HPeriod as  Period

Resident HISTORYAGEING;

LET NumOfPeriods = NoOfRows('TempPeriod');

After that use For .. Next loop (from 0 to NumOfPeriods-1), using peek() within a loop to get the Period.  Order doesn't matter at all.  It is something like:

FOR r = 0 to $(VRows)-1

LET vPeriod=peek('HPeriod', $(r), 'TempPeriod');

TempData:
LOAD * Resident HISTORYAGEING where HPeriod = $(vPeriod);
STORE TempData into HISTORYAGEING_$(vPeriod).qvd;

Drop table TempData;

NEXT

DROP TABLE TempPeriod

[#7]

Is this it? Sorry, my mind is really scrambled at this stage:

TempPeriod:

LOAD distinct HPeriod as Period

Resident HISTORYAGEING;

LET NumOfPeriods = NoOfRows('TempPeriod');

//DROP TABLE TempPeriod;

FOR NumOfPeriods = 0 to -1

Noconcatenate TempData:

LOAD * Resident HISTORYAGEING where HPeriod = Peek('Period');

STORE TempData into HISTORYAGEING_$(vPeriod).qvd (QVD);

Drop table TempData;

NEXT

On Mon, May 11, 2015 at 3:51 PM, Michael Solomovich <

[#8]

Michael

I am stuck on this one, getting the peek into the next without ending up

with what I had befoe, I will really appreciate your help just getting my

head around this one

[Anonymous]

My mistyping

TempPeriod:

LOAD distinct HPeriod as  Period

Resident HISTORYAGEING;

LET NumOfPeriods = NoOfRows('TempPeriod');

FOR r = 0 to $(NumOfPeriods)-1

LET vPeriod=peek('HPeriod', $(r), 'TempPeriod');

TempData:
LOAD * Resident HISTORYAGEING where HPeriod = $(vPeriod);
STORE TempData into HISTORYAGEING_$(vPeriod).qvd;

Drop table TempData;

NEXT

DROP TABLE TempPeriod

[Anonymous · Accepted solution]

Attached is a working example.  I had to add back one noconcatenate after all.

[#11]

Hi Michael, thanks a million, you have been a tremendous help, it's working perfectly. Thanks for your time, can't tell you how much I appreciate it.

Andre