topic Re: Load qvd by timestamp in script in QlikView

Load qvd by timestamp in script

markgraham123 — Thu, 09 Jul 2015 23:37:31 GMT

Hi,

I'm working on areq. where there were 20-30 Qvd files with same name but diff. time stamp.

YYYYMMDDFIlename.Qvd

Need to select the latest Qvd for the reload.

Can anyone pls help me in loading the latest qvd dynamically.

Re: Load qvd by timestamp in script

sunny_talwar — Thu, 09 Jul 2015 23:47:52 GMT

May be this:

Table:

LOAD FileName() as File,

Date#(Left(FileName(), 8), 'YYYYMMDD') as Date

FROM

[*Filename.qvd] (qvd);

Max:

LOAD Max(Date) as MaxDate

Resident Table;

LET vMaxDate = Date(Peek('MaxDate'), 'YYYYMMDD');

DROP Tables Table, Max;

FinalLoad:

LOAD *

FROM $(vMaxDate)&'Filename.qvd (qvd);

This might not be perfect because I have not tested it out, but the idea smay guide you in the right direction.

HTH

Best,

Sunny

Re: Load qvd by timestamp in script

markgraham123 — Thu, 09 Jul 2015 23:56:45 GMT

Hi all,

FYI

20150708ABCDEN00.qvd

20150707ABCDEN00.qvd

20150706ABCDEN00.qvd

20150705ABCDEN00.qvd

are the filenames

Re: Load qvd by timestamp in script

sunny_talwar — Fri, 10 Jul 2015 02:30:35 GMT

Try this script:

Table:

First 1

LOAD FileName() as File,

Date#(Left(FileName(), 8), 'YYYYMMDD') as Date

FROM *ABCDEN00.qvd (qvd);

MaxDate:

LOAD Max(Date) as MaxDate

Resident Table;

LET vMaxDate = Date(Peek('MaxDate'), 'YYYYMMDD');

DROP Tables Table, MaxDate;

FinalTable:

LOAD *

FROM $(vMaxDate)ABCDEN00.qvd (qvd);

HTH

Best,

Sunny

Re: Load qvd by timestamp in script

MayilVahanan — Fri, 10 Jul 2015 03:50:36 GMT

Try like this

Table:

LOAD Max(Date) as MaxDate;

LOAD Date#(Left(FileName(), 8), 'YYYYMMDD') as Date

FROM *ABCDEN00.qvd (qvd);

LET vMaxDate = Date(Peek('MaxDate'), 'YYYYMMDD');

DROP Tables Table;

FinalTable:

LOAD *

FROM $(vMaxDate)ABCDEN00.qvd (qvd);

Re: Load qvd by timestamp in script

sunny_talwar — Fri, 10 Jul 2015 09:58:05 GMT

Oh so I guess you are suggesting to calculate max date in preceding load rather than doing it in a resident load. That would make sense. But are there any performance benefits to this as well??

Best,

Sunny

Re: Load qvd by timestamp in script

markgraham123 — Fri, 10 Jul 2015 16:19:42 GMT

Hi Ramasamy,

When i try the above code, its not getting the date into Maxdate, and hence no data in $(MaxDate)...

Its failing

Re: Load qvd by timestamp in script

sunny_talwar — Fri, 10 Jul 2015 16:22:17 GMT

Did you try my script Mark?

Table:

First 1

LOAD FileName() as File,

Date#(Left(FileName(), 8), 'YYYYMMDD') as Date

FROM *ABCDEN00.qvd (qvd);

MaxDate:

LOAD Max(Date) as MaxDate

Resident Table;

LET vMaxDate = Date(Peek('MaxDate'), 'YYYYMMDD');

DROP Tables Table, MaxDate;

FinalTable:

LOAD *

FROM $(vMaxDate)ABCDEN00.qvd (qvd);

Re: Load qvd by timestamp in script

markgraham123 — Fri, 10 Jul 2015 16:24:40 GMT

Hi Sunny,

Its working.

But the problem is its loading all the qvds from the past few years, and giving me the right result.

but, can we restrict it to current year and month (Dynamically) so that the load time will be less else its taking more than 200 qvds so as to select the right one.

Re: Load qvd by timestamp in script

sunny_talwar — Fri, 10 Jul 2015 16:28:39 GMT

If you know that the most recent version will always be Today() - x days, then you can try using this:

Date:

LOAD Num(Today() - x) as Date //where x needs to be an integer

Resident Table;

LET vMaxDate = Date(Peek('MaxDate'), 'YYYYMMDD');

DROP Tables Table, MaxDate;

FinalTable:

LOAD *

FROM $(vMaxDate)ABCDEN00.qvd (qvd);

Re: Load qvd by timestamp in script

markgraham123 — Fri, 10 Jul 2015 16:37:32 GMT

Hi Sunny,

Can i know why is it mentioned Resident Table in Date:

When we r nt fetching anything from table.

Re: Load qvd by timestamp in script

sunny_talwar — Fri, 10 Jul 2015 16:38:50 GMT

My bad:

Date:

LOAD Num(Today() - x) as Date //where x needs to be an integer

AutoGenerate 1;

LET vMaxDate = Date(Peek('MaxDate'), 'YYYYMMDD');

DROP Tables Table, MaxDate;

FinalTable:

LOAD *

FROM $(vMaxDate)ABCDEN00.qvd (qvd);

Re: Load qvd by timestamp in script

markgraham123 — Fri, 10 Jul 2015 16:39:25 GMT

Is this right?

Table:

First 1

LOAD FileName() as File,

Date#(Left(FileName(), 8), 'YYYYMMDD') as Date

FROM *ABCDEN00.qvd (qvd);

MaxDate:

LOAD Max(Date) as MaxDate

Resident Table;

Date:

LOAD Num(Today() - x) as Date //where x needs to be an integer

Resident Table;

LET vMaxDate = Date(Peek('MaxDate'), 'YYYYMMDD');

DROP Tables Table, MaxDate;

FinalTable:

LOAD *

FROM $(vMaxDate)ABCDEN00.qvd (qvd);

Re: Load qvd by timestamp in script

sunny_talwar — Fri, 10 Jul 2015 16:41:02 GMT

No just use this:

Replace x with the number of days back you will have your most recent qvd to be. If its today's qvd then x = 0, yesterdays x = 1 and so on...

Date:

LOAD Num(Today() - x) as Date //where x needs to be an integer

AutoGenerate 1;

LET vMaxDate = Date(Peek('MaxDate'), 'YYYYMMDD');

DROP Tables Table, MaxDate;

FinalTable:

LOAD *

FROM $(vMaxDate)ABCDEN00.qvd (qvd);

Re: Load qvd by timestamp in script

markgraham123 — Fri, 10 Jul 2015 16:42:51 GMT

So, in this case, we donot need to load the "Table" at first where we r using Drop Tables Table, Date;

Re: Load qvd by timestamp in script

sunny_talwar — Fri, 10 Jul 2015 16:44:52 GMT

Yup, you are right, no need to drop Table Just use Drop Table Date;

Re: Load qvd by timestamp in script

sunny_talwar — Fri, 10 Jul 2015 16:45:05 GMT

Making too many silly mistakes today

Re: Load qvd by timestamp in script

markgraham123 — Fri, 10 Jul 2015 16:52:35 GMT

Re: Load qvd by timestamp in script

sunny_talwar — Fri, 10 Jul 2015 16:54:26 GMT

Can you paste a screenshot of your script, where you are doing this:

Date:

LOAD Num(Today() - x) as Date

AutoGenerate 1;

LET vMaxDate = Date(Peek('MaxDate'), 'YYYYMMDD');

Re: Load qvd by timestamp in script

markgraham123 — Fri, 10 Jul 2015 16:56:20 GMT

Sunny, its working

Small editing...

Its LET vMaxDate = Date(Peek('Date'), 'YYYYMMDD');

we were using LET vMaxDate = Date(Peek('MaxDate'), 'YYYYMMDD');

where der is no 'MaxDate'.. we have only 'Date'