topic Re: Use max date to load a qvd file in QlikView

Use max date to load a qvd file

agomes1971 — Mon, 07 Jul 2014 16:37:50 GMT

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é

Re: Use max date to load a qvd file

its_anandrjs — Mon, 07 Jul 2014 17:22:30 GMT

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);

Re: Use max date to load a qvd file

agomes1971 — Mon, 07 Jul 2014 17:44:32 GMT

It doesn't work? Any more suggestions?

Kind Regards

André Gomes

Re: Use max date to load a qvd file

Mon, 07 Jul 2014 18:17:54 GMT

Hi, what does "Data" field contain?

Re: Use max date to load a qvd file

chematos — Mon, 07 Jul 2014 18:22:08 GMT

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

Re: Use max date to load a qvd file

agomes1971 — Tue, 08 Jul 2014 08:40:01 GMT

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

Thanks any way

Re: Use max date to load a qvd file

chematos — Tue, 08 Jul 2014 12:18:55 GMT

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

Re: Use max date to load a qvd file

agomes1971 — Tue, 08 Jul 2014 13:40:47 GMT

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

Thanks

André Gomes

Re: Use max date to load a qvd file

chematos — Tue, 08 Jul 2014 13:56:57 GMT

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

Re: Use max date to load a qvd file

richard_pearce6 — Tue, 08 Jul 2014 14:10:58 GMT

Try putting your variable within a text() function.

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

Richard

Re: Use max date to load a qvd file

richard_pearce6 — Tue, 08 Jul 2014 14:17:05 GMT

Andre,

For a detailed explination on how to load specific dates from QVD's which are split into monthly files please see this blog: http://community.qlik.com/docs/DOC-6668

I can only assume the error you have is not loading the file (which is why the previous line is commented out), on my example this is overcome by storing the information as text before attempting to use it in the file name.

Regards

Richard