Discussion Board for collaboration related to QlikView App Development.
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
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.
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.
Thanks Jonathan D - however, not quite following the "assumes a legacy load and would need adaptation to use the lib:// syntax?"
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.
Understood. Needs some fiddling with, but looks like this will work in the use case I'm building.
Thanks!
Jonathan
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
;
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
;
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.
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
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)));