Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi together,
according to this script:
TEMP_PICKER_QMCLog:
LOAD Distinct FilePath() AS FileName, FileTime() AS DateFile
FROM D:\Qlikview11\DistributionService\1\Log\Root_201*.txt;
INNER JOIN (TEMP_PICKER_QMCLog) LOAD max(DateFile) as DateFile RESIDENT TEMP_PICKER_QMCLog;
LET PointerToQMCLogfile = peek('FileName',-1);
//DROP TABLE TEMP_PICKER_QMCLog;
QMCLogfile:
LOAD @1,
@3,
@4
FROM '$(PointerToQMCLogfile)' (txt, utf8, no labels, delimiter is ',', msq);
i am loading the latest file in qlikview. Nowadays the day before should also additionally be loaded in Qlikview.
I tried it with max(DateFile,2). But unfortunately, only one file will be loaded. How do I implement it so that only the last two days will be loaded ?
Thank you for your advice.
Best Regards,
Hi togehter,
i got a solution from Clever. Many thanks to him.
SET PATH='D:\Qlikview11\DistributionService\1\Log';
LET TwoDays=date(today()-2,'YYYYMMDD');
for each file in FileList('$(PATH)\Root_*.txt')
let filebasename =subfield('$(file)','\',-1);
if '$(filebasename)' follows 'Root_$(TwoDays).txt' then
QMCLog:
LOAD
@1 AS QMCLogDate,
@3 AS QMCLogType,
@5 AS QMCLogTask,
@6 AS QMCLogApp,
@7 AS QMCLogInfo1,
@8 AS QMCLogInfo2
FROM
(txt, utf8, no labels, delimiter is spaces, msq, no eof);
end if
next
This script load only the last two days and works perfectly.
Perhaps something like this:
TEMP_PICKER_QMCLog:
LOAD Distinct FilePath() AS FileName, FileTime() AS DateFile
FROM D:\Qlikview11\DistributionService\1\Log\Root_201*.txt;
Temp2:
First 2load * resident TEMP_PICKER_QMCLog
order by DateFile desc;
let vLatestFile = peek('FileName',0,'Temp2');
let vSecondLatestFile = peek('FileName',1,'Temp2');
LOAD * from '$(vLatestFile)' (txt, utf8, no labels, delimiter is ',', msq);
LOAD * from '$(vSecondLatestFile)' (txt, utf8, no labels, delimiter is ',', msq);
If you truly run reloads daily and the max() works why not do max()-1 for the previous day?
Hi Gysbert,
your adviced script loads regrettably all Files !
I need only the last 2 days listet. Actually today (30.10.2013) and yesterday(29.10.2013)
Thank you
Best Regards,
Hi Andrew,
i tried it with LOAD max(DateFile)-1 but it did not works.
Thank you,
Best Regards
Thats most likely because your doing an inner join and using the max function the max will always only return a single result.
Why not use the max and the peek to get the latest date minus one and store it into a variable. Then inner join the table to itself with just the distinct PK and use a where condition to say date >= $(#maxDateMinus1) or something like that.
Onur Civelek wrote:
Hi Gysbert,
your adviced script loads regrettably all Files !
I need only the last 2 days listet. Actually today (30.10.2013) and yesterday(29.10.2013)
Could you double check this? I doubt that all files are loaded when using Gysbert's script snippet.
Your DateFile values will still show all available file dates, because you haven't inner joined nor dropped this field.
Could you check which files are loaded e.g. by posting the document log?
Hello again,
maybe my script is not suitable for my project.
The QMC generates the following logs. I want to load only the last 2 days. In this case today and yesterday:
Root_20131030.txt
Root_20131029.txt
Is there a more appropriate way or an example script to do this ?
Thank you very much !
I do still assume that Gysbert's script works or can be made working easily, have you already checked my above statement?
There are lots of examples how you can load a list of available files into QV, then filter by date or extension and loop through the remaining set to load the data files in.
For example:
Hi togehter,
i got a solution from Clever. Many thanks to him.
SET PATH='D:\Qlikview11\DistributionService\1\Log';
LET TwoDays=date(today()-2,'YYYYMMDD');
for each file in FileList('$(PATH)\Root_*.txt')
let filebasename =subfield('$(file)','\',-1);
if '$(filebasename)' follows 'Root_$(TwoDays).txt' then
QMCLog:
LOAD
@1 AS QMCLogDate,
@3 AS QMCLogType,
@5 AS QMCLogTask,
@6 AS QMCLogApp,
@7 AS QMCLogInfo1,
@8 AS QMCLogInfo2
FROM
(txt, utf8, no labels, delimiter is spaces, msq, no eof);
end if
next
This script load only the last two days and works perfectly.