Use max date to load a qvd file

[agomes1971]

Why with this code isn't getting the file for month 3?

//Carregamento Inicial dos dois primeiros meses do ano

TESTE:

LOAD Name,

     Data

FROM

(qvd);

Concatenate (TESTE)

LOAD Name,

     Data

FROM

(qvd);

TEMP:

LOAD

min(Data) AS MaxDate

RESIDENT TESTE;

LET vMaxDate = date(floor(peek('MaxDate',0,'TEMP')),'YYYY-MM-DD');

LET vMaxMonth = num(month($(vMaxDate))+1,'00');

//Carregamento parcelar

TESTE:

//Add only load * from C:\Users\red_agomes\Desktop\case_2014-03.qvd (qvd);

Add only load * from C:\Users\red_agomes\Desktop\case_2014-$(vMaxMonth).qvd (qvd);

What am i missing?

Regards

André

[its_anandrjs]

I believe you use Min in the TEMP table you have to use Max in the script

TEMP:

LOAD

min(Data) AS MaxDate  //You have to use Max(Data) AS MaxDate

RESIDENT TESTE;

And your complete statement is

=====================

TESTE:

LOAD Name,

     Data

FROM

(qvd);

Concatenate (TESTE)

LOAD Name,

     Data

FROM

(qvd);

TEMP:

LOAD

Max(Data) AS MaxDate

RESIDENT TESTE;

LET vMaxDate = date(floor(peek('MaxDate',0,'TEMP')),'YYYY-MM-DD');

LET vMaxMonth = num(month($(vMaxDate))+1,'00');

//Carregamento parcelar

TESTE:

//Add only load * from C:\Users\red_agomes\Desktop\case_2014-03.qvd (qvd);

Add only load * from C:\Users\red_agomes\Desktop\case_2014-$(vMaxMonth).qvd (qvd);

[agomes1971]

It doesn't work? Any more suggestions?

Kind Regards

André Gomes

[#4]

Hi, what does "Data" field contain?

[chematos]

Try this:

TESTE:

LOAD Name,

     Data

FROM

(qvd);

Concatenate (TESTE)

LOAD Name,

     Data

FROM

(qvd);

LET vMaxDate = date(floor(peek('Data')),'YYYY-MM-DD');  //this should get the last data loaded

LET vMaxMonth = num(month($(vMaxDate))+1,'00');

//you should debug and see the values of your variables.

TESTE2:

NoConcatenate

load * from C:\Users\red_agomes\Desktop\case_2014-$(vMaxMonth).qvd (qvd);

hope this helps

[agomes1971]

Doesn´t work because the code always refers to the current month...

Thanks any way

[chematos]

Could you explain a little more the context of what do you need?

[agomes1971]

I need to do a partial reload with the qvd file of the next month relative to the last month uploaded.

Thanks

André Gomes

[chematos]

Then, I don't understand why do you say the solution would not work. This is the scenario I am thinking:

You already have a QVD with the initial data previously loaded, for example, you have data in TESTE table until previous month, June. This data should be loaded from a historical QVD, for example case_histoy.qvd.

TESTE:

LOAD Name,

     Data

FROM

(qvd);

Then you set a variable with max date and a variable with the next month:

TEMP:

LOAD distinct

Max(Data) AS MaxDate

RESIDENT TESTE;

// make sure this gets you the max  date and make sure the date format is correct when you set the variables. You can use the mid() function instead of month() if vMaxMonth didn't work.

LET vMaxDate = date(floor(peek('MaxDate',0,'TEMP')),'YYYY-MM-DD');

LET vMaxMonth = num(month($(vMaxDate))+1,'00');

Finally, you concatenate the new data and you shoul store the table into the historical qvd to update the data so you can use this QVD in your application. I am assuming this script ir for the loading process in order to update the QVDs you will use in your dashboards. Anyway, you need to update the historical QVD with the new data you add every month.

concatenate(TESTE)

load * from C:\Users\red_agomes\Desktop\case_2014-$(vMaxMonth).qvd (qvd);

store * from TESTE into C:\Users\red_agomes\Desktop\case_historical.qvd (qvd);

If I am missunderstanding something, sorry but this is what make sense for me.

Hope this helps, regards

[richard_pearce6]

Try putting your variable within a text() function.

LET vMaxMonth = text(num(month($(vMaxDate))+1,'00'));

Richard