Creating history from old excel files

Fri, 20 Nov 2020 18:02:30 GMT

I am having to load some old history from xml files. I need to create a qvd file from 2 different xml files for everyday.

I know how to load the 2 files and save them as a .qvd file. But I am having to do this very manually and was wanting to know if there is any way to automatically have in run and save off this file for every date for the history and save the name with date that is listed in the files. I hope I am making sense here.

Here is what I am currently doing:

History2015:

LOAD DATE,

w12,

w11,

w10,

w9,

w8,

w7,

w6,

w5,

w4,

w3,

w2,

w1,

min,

max

FROM

[\\LINK\Com 01-01-15.xlsm]

(ooxml, embedded labels, header is 1 lines, table is [OLD]);;

CONCATENATE(History2015)

LOAD DATE,

w12,

w11,

w10,

w9,

w8,

w7,

w6,

w5,

w4,

w3,

w2,

w1,

min,

max

FROM

[\\LINK\Distribution\2015\201501\Dis 01-01-15.xlsm]

(ooxml, embedded labels, header is 1 lines, table is [OLD]);

Store * From History2015 into Datasources/History/History_.qvd (qvd);

Drop Table History2015;

For all the different days there is always a Dis (date) and Com (date).

Is there any way for me to have qlikview go through all of them and run it without me have to run it and go in and change the file date and run it again? Also is there a way for me to have where it stores in and names it History_ to add the date that is in the file I am loading? because for like the example above I am then having to go back in and rename it to History_01-01-15.qvd

Thank you for any help!

Re: Creating history from old excel files

Thu, 22 Oct 2015 19:46:01 GMT

Hi, we can use the variables in the file names & change the variable value on each reload based on the new file creation methodology.

Let say the the file was created on every day & file name have each date. I need to run my Qlikview job & store back to QVD.

Let vComFileName = '\\LINK\Com ' & Date(Today(),'MM-DD-YYYY') & '.xlsm' ; // because of the Today() the file name changes automatically with each day file.

Let vDisFileName = '\\LINK\Distribution\' & Year(Today()) &'\' & Date(Today(),'YYYYMM') & '\Dis ' & Date(Today(),'MM-DD-YYYY') & '.xlsm' ;

History:

LOAD DATE,

w12,

w11,

w10,

w9,

w8,

w7,

w6,

w5,

w4,

w3,

w2,

w1,

min,

max

FROM

[vComFileName]

(ooxml, embedded labels, header is 1 lines, table is [OLD]);;

CONCATENATE(History)

LOAD DATE,

w12,

w11,

w10,

w9,

w8,

w7,

w6,

w5,

w4,

w3,

w2,

w1,

min,

max

FROM

[vDisFileName]

(ooxml, embedded labels, header is 1 lines, table is [OLD]);

Let vQVDName = 'Datasources/History/History_'&Date(Today(),'MM-DD-YYYY') ;

Store History into [$(vQVDName ).qvd] (qvd);

Drop Table History;

topic Creating history from old excel files in QlikView

Creating history from old excel files

Re: Creating history from old excel files