topic Re: LOAD only most current Excel file from list of files in QlikView

LOAD only most current Excel file from list of files

schneider2037 — Wed, 25 Nov 2020 16:16:04 GMT

All:
Have a data source that is a daily feed of an XLSX whereby the filename is listed with an epoch suffix. I need to somehow load on a daily basis the file with the max of the epoch timestamp. Can't rely on a date stamp because sometimes the files are delayed and come over as a day late.

Figure if I can get to a max of the epoch time, and load ONLY the file that corresponds to that value, then that should resolve any issues with delayed reporting or having to manually change the filename daily.

Filename examples:

Service_Issue_Calls-Past7days_1528023666940 <--(most current file, the only one I want to load "today")

Service_Issue_Calls-Past7days_1527967390521

Service_Issue_Calls-Past7days_1527967370343

Service_Issue_Calls-Past7days_1527764450215

and so on...

I know I need to create a pathway variable and a filename variable, combined into the LOAD string somewhere, but SUPER new to QV still, and while I've read as many samples online as I can, still struggling. Any suggestions?

Thanks,

Jonathan

Re: LOAD only most current Excel file from list of files

jonathandienst — Mon, 04 Jun 2018 11:23:25 GMT

Use this code pattern:

vMax = 0;

For Each vFile in FileList('Service_Issue_Calls-Past7days_*')

Let vMax = RangeMax(vMax, Right(vFile, 13));

Next

Let vFile = 'Service_Issue_Calls-Past7days_' & vMax;

LOAD *

FROM [$(vFile)] (....);

Adapt to your specific requirements. The file mask in the FileList may need to include a directory path. Assumes a legacy load and would need adaptation to use the lib:// syntax.

Re: LOAD only most current Excel file from list of files

schneider2037 — Mon, 04 Jun 2018 11:28:27 GMT

Thanks Jonathan D - however, not quite following the "assumes a legacy load and would need adaptation to use the lib:// syntax?"

Re: LOAD only most current Excel file from list of files

jonathandienst — Mon, 04 Jun 2018 15:20:35 GMT

Qlikview uses a simple file path to file sources. By default, QS uses file connections set up in the app or on the server and pointed to via the lib://connection syntax.

Re: LOAD only most current Excel file from list of files

schneider2037 — Mon, 04 Jun 2018 16:20:45 GMT

Understood. Needs some fiddling with, but looks like this will work in the use case I'm building.

Thanks!

Jonathan

Re: LOAD only most current Excel file from list of files

schneider2037 — Mon, 04 Jun 2018 19:43:57 GMT

Jonathan D - a bit premature on my part working through the directory path.

\\Ftvqv002\d\From_Linuxmnt is where the files "live."

But no matter the tweaking can't get vFile to be anything other than 'Service_Issue_Calls-Past7days_0.XLXS.

Below is what I'm running right now (again...I need to emphasize that I'm about as big of a rookie on this as you can imagine! I feel I'm missing the forest for the trees in the way)

**************************************************************************************************************************

vMax = 0;

For Each vFile in FileList('\\Ftvqv002\d\From_Linuxmnt\Service_Issue_Calls-Past7days_*')

Let vMax = RangeMax(vMax, Right(vFile, 13));

Let vFile = 'Service_Issue_Calls-Past7days_' & vMax;

ServiceIssuesCalls:

LOAD

ISSUE_ID,

ISSUE_CATEGORY,

COMMENT_TYPE,

CALL_ID,

CALL_ACTION,

CALL_CONTACT_NAME,

CALL_SERVICE_REP,

CALL_COUNT,

Date(CALL_DATE) As CALL_DATE,

DEALER,

ISSUE_ID & '|' & CALL_ID As ROW_ID,

SubField(FileName(),'.',1) As FILE_NAME_REFERENCE,

num(LEFT(RIGHT(FileBaseName(),13),10)) As FILE_EPOCH_TIME

FROM

[[\\Ftvqv002\d\From_Linuxmnt\$(vFile).XLSX]

(ooxml, embedded labels, header is 1 lines, table is Page1_1)

Where (ISSUE_CATEGORY) = 'ProductSupport' and Len(CALL_DATE) > 0

;

Re: LOAD only most current Excel file from list of files

schneider2037 — Mon, 04 Jun 2018 19:45:43 GMT

Jonathan D - a bit premature on my part working through the directory path.

\\Ftvqv002\d\From_Linuxmnt is where the files "live."

But no matter the tweaking can't get vFile to be anything other than 'Service_Issue_Calls-Past7days_0.XLXS.

Below is what I'm running right now (again...I need to emphasize that I'm about as big of a rookie on this as you can imagine! I feel I'm missing the forest for the trees in the way)

**************************************************************************************************************************

vMax = 0;

For Each vFile in FileList('\\Ftvqv002\d\From_Linuxmnt\Service_Issue_Calls-Past7days_*')

Let vMax = RangeMax(vMax, Right(vFile, 13));

Let vFile = 'Service_Issue_Calls-Past7days_' & vMax;

ServiceIssuesCalls:

LOAD

ISSUE_ID,

ISSUE_CATEGORY,

COMMENT_TYPE,

CALL_ID,

CALL_ACTION,

CALL_CONTACT_NAME,

CALL_SERVICE_REP,

CALL_COUNT,

Date(CALL_DATE) As CALL_DATE,

DEALER,

ISSUE_ID & '|' & CALL_ID As ROW_ID,

SubField(FileName(),'.',1) As FILE_NAME_REFERENCE,

num(LEFT(RIGHT(FileBaseName(),13),10)) As FILE_EPOCH_TIME

FROM

[[\\Ftvqv002\d\From_Linuxmnt\$(vFile).XLSX]

(ooxml, embedded labels, header is 1 lines, table is Page1_1)

Where (ISSUE_CATEGORY) = 'ProductSupport' and Len(CALL_DATE) > 0

;

Re: LOAD only most current Excel file from list of files

jonathandienst — Tue, 05 Jun 2018 05:45:37 GMT

I think your files have an xlsx extension. That was not clear from your original posting. This line assumes no file extension:

Let vMax = RangeMax(vMax, Right(vFile, 13));

Change to this to trim extension before extracting the number:

Let vMax = RangeMax(vMax, Right(subField(vFile, '\', -1), 13));

You could add a trace command after the Let to see what you really got.

Trace $(vMax);

This will show up in the document reload log and the reload progress screen. It is also useful in debug mode.

Re: LOAD only most current Excel file from list of files

schneider2037 — Tue, 05 Jun 2018 11:01:27 GMT

Progress of sorts!

*******************************************************************************************
Script:

vMax = 0;

For Each vFile in FileList('\\Ftvqv002\d\From_Linuxmnt\Service_Issue_Calls-Past7days_*')

Let vMax = RangeMax(vMax, Right(subField(vFile, '\', -1), 13));

Let vFile = 'Service_Issue_Calls-Past7days_' & vMax;

Trace $(vMax);

...

FROM

[\\Ftvqv002\d\From_Linuxmnt\$(vFile).XLSX]

(ooxml, embedded labels, header is 1 lines, table is Page1_1)

********************************************************************************************

OK - the log is showing that there's a scan for the 26 files this morning that start with "Service_Issue_Calls-Past7days_*" This is good, but Trace is returning 0. Never used trace before so unsure if the results below are expected (or if there should be "values" returned?).

Very much appreciate your thoughts, sir. Feel like

from the log:

6/5/2018 6:51:49 AM: 0056 vMax = 0

6/5/2018 6:51:49 AM: 0058 For Each vFile in FileList('\\Ftvqv002\d\From_Linuxmnt\Service_Issue_Calls-Past7days_*')

6/5/2018 6:51:49 AM: 0060 Let vMax = RangeMax(vMax, Right(subField(vFile, '\', -1), 13))