Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
schneider2037
Creator
Creator

LOAD only most current Excel file from list of files

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

1 Solution

Accepted Solutions
jonathandienst
Partner
Partner

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

11 Replies
jonathandienst
Partner
Partner

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
Creator
Creator
Author

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

jonathandienst
Partner
Partner

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
Creator
Creator
Author

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

Thanks!

Jonathan

schneider2037
Creator
Creator
Author

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

Next

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

;

vFile Error.JPG

schneider2037
Creator
Creator
Author

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

Next

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

;

vFile Error.JPG

jonathandienst
Partner
Partner

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
Creator
Creator
Author

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

Next

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

6/5/2018 6:51:49 AM: 0061  Next

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

6/5/2018 6:51:49 AM: 0061  Next

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

6/5/2018 6:51:49 AM: 0061  Next

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

6/5/2018 6:51:49 AM: 0061  Next

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

6/5/2018 6:51:49 AM: 0061  Next

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

6/5/2018 6:51:49 AM: 0061  Next

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

6/5/2018 6:51:49 AM: 0061  Next

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

6/5/2018 6:51:49 AM: 0061  Next

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

6/5/2018 6:51:49 AM: 0061  Next

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

6/5/2018 6:51:49 AM: 0061  Next

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

6/5/2018 6:51:49 AM: 0061  Next

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

6/5/2018 6:51:49 AM: 0061  Next

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

6/5/2018 6:51:49 AM: 0061  Next

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

6/5/2018 6:51:49 AM: 0061  Next

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

6/5/2018 6:51:49 AM: 0061  Next

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

6/5/2018 6:51:49 AM: 0061  Next

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

6/5/2018 6:51:49 AM: 0061  Next

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

6/5/2018 6:51:49 AM: 0061  Next

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

6/5/2018 6:51:49 AM: 0061  Next

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

6/5/2018 6:51:49 AM: 0061  Next

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

6/5/2018 6:51:49 AM: 0061  Next

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

6/5/2018 6:51:49 AM: 0061  Next

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

6/5/2018 6:51:49 AM: 0061  Next

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

6/5/2018 6:51:49 AM: 0061  Next

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

6/5/2018 6:51:49 AM: 0061  Next

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

6/5/2018 6:51:49 AM: 0061  Next

6/5/2018 6:51:49 AM: 0062  Let vFile = 'Service_Issue_Calls-Past7days_' & vMax

6/5/2018 6:51:49 AM: 0063  Trace 0

schneider2037
Creator
Creator
Author

Jonathan - looking like the return to get the epoch piece off the filename is not being returned as a numeric value, as Trace is always returning 0 (only numeric number in comparison).

I've tried without success wrapping num() around it. Wondering if there's a way to simply multiply by 1? I very much appreciate your time/thoughts, sir.

vMax = 0 (recognized in RangeMax as numeric)

The right side of the expression still not returning as numeric

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