topic Re: QVD export and load in QlikView

QVD export and load

Thu, 02 Apr 2015 10:32:45 GMT

I need to do the following, would appreciate any ideas about the best way to do this.

I have a Model which loads data per month. On the first run I need it to export data to qvd files per month example qvd201501.qvd, qvd201502.qvd etc.

On the second run it should only run the current month, and export to qvd, with this exception. On the 1st of May for example it still needs to run the data for April, 2nd of May it should export for May only.

Then in the actual model for the Users, I need to automatically import the last 12 month's qvds only

Re: QVD export and load

pokassov — Thu, 02 Apr 2015 11:21:42 GMT

Hi, Andret!

Would you try my expamle?

Sergey.

let DTStart=MakeDate(2011,1,1);

let DTEnd=Today(1);

let YearStart=Year(DTStart);

let YearEnd=Year(DTEnd);

let MonthEnd=Month(DTEnd);

for i=YearStart to YearEnd

for j=1 to 12

//documents

Set FileName=documents_$(i)_$(j).qvd;

set TableName=documents;

if (MakeDate(i,j,1)<=DTEnd and isnull(QvdCreateTime('$(QVDPath)\$(FileName)'))) or (i=YearEnd and j=MonthEnd) then

$(TableName):

sql select

--top 1000

doc_date "DateTime",

dateadd(day, datediff(day, 0, doc_date), 0) "Date",

--doc_date_only

doc_id "IDDocument",

doc_num "NumDocument",

dt_id "IDDocType",

emp_id "IdEmployee"

from documents

where doc_date between dateadd(day,0,dateadd(month,$(j)-1,dateadd(year,$(i)-1900,0)))

and dateadd(second,59,dateadd(minute,59,dateadd(hour,23,dateadd(day,-1,dateadd(month,$(j),dateadd(year,$(i)-1900,0))))));

STORE $(TableName) into $(QVDPath)\$(FileName) (qvd);

Drop table $(TableName);

end if;

next j;

next i;

Re: QVD export and load

Thu, 02 Apr 2015 11:57:03 GMT

Thanks I will try, give me a few days, I will give feedback

Re: QVD export and load

Peter_Cammaert — Thu, 02 Apr 2015 11:57:54 GMT

Something like this?

// Make sure your source data is loaded as RESIDENT. Call this table SourceData with a field SD_Date

// Set QVDPath to the path of source/dest QVDs

FOR j = 0 to 11 // only cover 12 months

LET TableName = 'qvd$(=year(AddMonths(today(), $(j))))$(=num(Month(AddMonths(today(), $(j))), '00'))';

IF (j = 0) OR

((j = 1) AND (Day(today()) = 1)) OR

IsNull(FileTime('$(QVDPath)\$(TableName).QVD')) THEN

$(TableName):

LOAD * RESIDENT SourceData WHERE InMonth(SD_Date, today(), -$(j));

STORE $(TableName) INTO '$(QVDPath)\$(TableName).QVD';

DROP Table $(TableName);

END IF

Re: QVD export and load

Thu, 16 Apr 2015 08:07:24 GMT

I am not making sense of this personally probably this is because I still quite new to this.

I have a table DEBTORHISTORY with a field HPeriod., I need the setpath as well for the QVD's and then a way to split these field into files with one HPeriod per qvd

This is the path name

E:\Qlikview\QVDeploy\Debtor\QVD\RE0001

I would like to call the qvd's RE0001-"HPeriod"

For the first load I want to do a complete load and create of qvd's, after that i want to let's say only create a new qvd for the current and previous month which will overwrite the old qvds

Re: QVD export and load

Thu, 16 Apr 2015 13:54:35 GMT

Trying to get my head around this, will have a look at this again tomorrow when i have a clear head, I am starting to pick up from the two eplies, die logic is slowly starting to make sense

Re: QVD export and load

Peter_Cammaert — Thu, 16 Apr 2015 14:08:04 GMT

Feel free to ask any questions you may have. I admit that the code is a bit condensed at first sight

Re: QVD export and load

Fri, 17 Apr 2015 06:04:08 GMT

I want to do an initial load, that this stage it is unsure how far back we will go, this depends from one clients to the next. In the initial load then i need to export all the files to qvd, including the current month, then also on the first of the month I still need to run the previous months as well. So going forward from the initial load I will only be pulling in the current month's data and this then needs to overwrite the olde file created the previous day...

As I understand it the first step like you said is setting the path, that is easy enough, after that you lose me a bit with the 12 months only, that is probably changeable. I will be getting the name of the file from Text for example RE0001 and then a hyphen with the Hperiod field which indicated=s the month for which the QVD is.

Not sure why I need to load it as a resident able, is this basically to create a temporary table with only the months data which is to be exported to qvd and then you drop the tav=ble after the export, then you do to the next months etc?

The code itself would be a bit easier if you can explain in words what it does

FOR j = 0 to 11 // only cover 12 months--this can then probably be changed to how many months are required

LET TableName = 'qvd$(=year(AddMonths(today(), $(j))))$(=num(Month(AddMonths(today(), $(j))), '00'))';

IF (j = 0) OR

((j = 1) AND (Day(today()) = 1)) OR

IsNull(FileTime('$(QVDPath)\$(TableName).QVD')) THEN

$(TableName):--This bit from my previous comment I am unsure about, not sure anout the file time or why you specifically use j-0 or 1 with the first day of the month, obviously this is for the current month or if you are on the first day of the new month, I am just not sure exactly what the code is doing

LOAD * RESIDENT SourceData WHERE InMonth(SD_Date, today(), -$(j));

STORE $(TableName) INTO '$(QVDPath)\$(TableName).QVD';

DROP Table $(TableName);

END IF

Hope I am making sense. I really appreciate your help

Re: QVD export and load

Thu, 30 Apr 2015 07:02:46 GMT

Hi Peter

Can you please help here?

Re: QVD export and load

Thu, 30 Apr 2015 08:38:18 GMT

Sergey

I really like your idea, I just have a few questions. You set the dates etc fine, my question is regarding the timestamp field. I only have a YearMonth field.The in table in question we do not have a date added field as such, We have a process running which adds last months data on the first day of the new month to the source table from where we import the data, so there is not real datetime field. So for months prior to that the data is stored into qvd's on the first run, then every month on the first this model will basically create a new qvd file for the past month.

We will then use the qvd files to load it into another model where we have our dashboards.

Could you have a look at how I can change the script fot eh YearMonthField?

Re: QVD export and load

Peter_Cammaert — Thu, 30 Apr 2015 10:42:50 GMT

Sorry, been busy lately. I'll do my best.

12 months: yes you can change that. I didn't define a variable for the number of months you want to go bback because you stated that the final document will only load data from the last 12 months. Change the FOR loop start into something like:

SET vNumOfMonthsToGoBack = 24; // Now we get a run for 24 (preceding) months

FOR j = 0 TO $(vNumOfMonthsToGoBack) - 1

File naming: the LET statement in the FOR body assigns a different name to each successive table. If I understand it correctly, you can modify the LET statement into something like. This. Note that if you want to cover more than 12 months, you should include the year as well. That's what this LET statement does.

LET TableName = 'RE0001-$(=year(AddMonths(today(), $(j))))$(=num(Month(AddMonths(today(), $(j))), '00'))';

Resident source data: just to make my example code simpler. The LOAD is from a central RESIDENT table that contains all data. YMMV. BTW Your assumption about how it operates is correct.

Code comment 2: exactly, that's all there is to it. You create a QVD if one of these conditions is met:

For Current month (j = 0) =: always
For Previous month (j=1 and day = 1) = only on the first of the current month
For missing QVD = always. This is the big initial load.

Best,

Peter

Re: QVD export and load

Peter_Cammaert — Fri, 08 May 2015 18:32:18 GMT

Did you manage to get it to work?

Peter

Re: QVD export and load

Mon, 11 May 2015 10:21:10 GMT

Will get back to you, busy with some SQL DBA stuff as well

On Fri, May 8, 2015 at 8:33 PM, Peter Cammaert <qcwebmaster@qlikview.com>