Solved: Re: LOAD only most current Excel file from list of... - Qlik Community

schneider2037 · ‎2018-06-04

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

jonathandienst · ‎2018-06-04

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

jonathandienst · ‎2018-06-04

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

schneider2037 · ‎2018-06-04

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

jonathandienst · ‎2018-06-04

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

schneider2037 · ‎2018-06-04

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

Thanks!

Jonathan

schneider2037 · ‎2018-06-04

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));

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

;

schneider2037 · ‎2018-06-04

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));

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

;

jonathandienst · ‎2018-06-05

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

schneider2037 · ‎2018-06-05

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));

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_*')