topic Re: QlikView should pull automatically from "New Updated Excel Sheet" by latest date. in QlikView

QlikView should pull automatically from "New Updated Excel Sheet" by latest date.

Tue, 11 Nov 2014 11:21:42 GMT

Hi,

I've a Sales folder that contains sales related excel sheets coming in automatically on daily basis. How to get the latest one as my source file automatically?

Re: QlikView should pull automatically from "New Updated Excel Sheet" by latest date.

Tue, 11 Nov 2014 11:27:55 GMT

Ho w u are getting automatically. is there any job ?

load * from

\c\..\ sales.xl

where date >='11/11/2014';

Re: QlikView should pull automatically from "New Updated Excel Sheet" by latest date.

ali_hijazi — Tue, 11 Nov 2014 11:32:15 GMT

on every reload save the maximum date in a qvd File

if your table is T

SortT:

noconcatenate

load * resident T order by Date asc;

drop table T;

let vLastDate = peek('Date',-1,'SortT');

Save:

Load $(vLastDate) as LastDate;

Autogenerate 1;

Store Save into File.qvd;

then on the successive reloads read everything from database where date is > the max Value the you read from QVD file saved

Re: QlikView should pull automatically from "New Updated Excel Sheet" by latest date.

Tue, 11 Nov 2014 11:53:33 GMT

Yes, there is a job. this Sales folder is getting updated from SAP Database.

Re: QlikView should pull automatically from "New Updated Excel Sheet" by latest date.

Tue, 11 Nov 2014 11:56:43 GMT

where date >='11/11/2014' put this after laoding xl file

for automation put like

load *,

from sales.xls

where date >today()-1; it loads todays data if u want yesterdays data also then go like >=today()-1

Re: QlikView should pull automatically from "New Updated Excel Sheet" by latest date.

Tue, 11 Nov 2014 12:22:22 GMT

Hi,

Let vLast_reloadtime=reloadtime();

Table:

Load *

from Tablename where date>($vLast_reloadtime);

or else Let vToday=today();

Let vPath= 'C:/'&($vToday);

Table1:

sql select * from Tablename;

store Table1 into ($vPath).xls;

Re: QlikView should pull automatically from "New Updated Excel Sheet" by latest date.

shree909 — Tue, 11 Nov 2014 18:01:02 GMT

Try this

SalesFolder: //Here Your getting all the file names that are present in the source folder//

LOAD

// RecNo() AS Records,

FILENAME() AS KEY

FROM

[..\DATA\SALESREPORT *]

(ooxml, embedded labels, table is Sales);

MAX_FILE: // Using resident your loading only the file that has maxtimestamp//

LOAD

MaxString(KEY) AS MAX_FILE

RESIDENT SalesFolder;

LET vMaxFile=peek('MAX_FILE','-1','MAX_FILE'); // Storing that maxfile in a variable//

DROP TABLE SalesFolder;

SalesFolderWithMaxDate: // loading only the file that has max timestamp from the source folder//

lOAD *

FROM

[..\DATA\$(vMaxFile)]

(ooxml, embedded labels, table is Sales);

/* U can Create a task by daily or hourly ( On qlikview Server) how your files are updated on the source folder */

Let me know if this works

Thanks