QVD export and load

[#1]

Hi

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

[pokassov]

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;

[#3]

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

[Peter_Cammaert]

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

NEXT

[#5]

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

[#6]

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

[Peter_Cammaert]

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

[#8]

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

NEXT

Hope I am making sense. I really appreciate your help

[#9]

Hi Peter

Can you please help here?

[#10]

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?