Best way of naming convention according to my problem

[#1]

Hello,

Right now i'm spliting my SQL Dataset monthwise. And giving the names as 1,2,3,4,...12, this are qvd names. In another file i'm doing incremental load process. Now i'want to retrive Max date of last month which it is created in first qvw file. So in second file i'm using the code as:

sub DoDir (Root)

for each Ext in 'qvd'

for each File in filelist (Root&'\*.'&Ext)

Temp:

Load '$(File)' as Name

autogenerate 1;

next File

next Ext

for each Dir in dirlist (Root&'\*')

call DoDir (Dir)

next Dir

end sub

call DoDir ('$(vQVDPath)')

LET File = Peek('Name');

LET vFileName=Replace(SubField(File,'.'),vQVDPath&'\','');

LatestMonth:

LOAD *

FROM

Month\$(vFileName).qvd

(qvd);

- Now it works fine. If my SQL Dataset consists of data from Oct 2011 to March 2012 then i'll run my initial qvw file it generates the qvd files with names.

10.qvd

11.qvd

12.qvd

1.qvd

2.qvd

3.qvd

- After that if i run my incremental file i.e the code which i paste above will run and retrives 12.qvd file instead of 3.qvd.

How can i resolve this. Which is best way to give the qvd file names. One more thing is why i'm giving the monthnumber as qvd file name b'coz 1,2,3 are month numbers of 2012, 10,11,12 are from 2011. If i run my incremental qvw in month october 2012, it should overlap 10.qvd . That's why i'm taking month numbers instead of adding year for qvd files.

[jvitantonio]

Try adding the Year to the name. Like 2012_01.qvd or directly 201201

[#3]

If i add year i can't overlap the qvd files for next time. What i'm saying is i don't previous year month data.

Say for eg:

201111

201112

201201

201202

201203

.

.

.

.

201210

201211

- I don't want same month data of two yr's.

[jvitantonio]

You create the files like that and then you controle what you want to load in your script, with variables

[#5]

Ok instead of that i'll create last loaded qvd file name in one txt file or qvd file. But can you see the below code:

SQLData:

LOAD I*

SQL SELECT *

FROM "Split Data".dbo."Sheet2";

Temp1:

LOAD Distinct Month(DateTime)*1 AS MonthName1,

MonthName(DateTime) as MonthYear

Resident SQLData

;

Temp:

LOAD MonthName1 as MonthName

Resident Temp1

Order by MonthYear asc

;

Let vMax=FieldValueCount('MonthName');

FOR i=0 to $(vMax)-1

LET vMonth = Peek('MonthName',$(i),'Temp');

Next

- I can able to catch last loaded file name in vMonth variable.

- But for first iteration in vMonth i'm getting Null value. It shows in debug window as vMonth<NULL>. I don't why it is coming null for first iteration.

I tried

Load

if(len(MonthName1)>0, MonthName1) as MonthName

it is also showing null value for first iteration.

Load if(MonthName1<>'',MonthName1) as MonthName

Same problem if i try above code. Is there any way for this.

[#6]

Perhaps you can try the following number scheme:

1 - current month

2 - previous month

...

12 - 12 months ago

This way you don't have to track the current month; it is always one. And it should be simple to either generate or only load 12 months of data.

If you load your data in reverse chronological order and autonumber on year-month then you can create the numbers for your qvd names.