LOAD Max(DateFile)

[#1]

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,

[Gysbert_Wassenaar]

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 2
load * 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); 

[andrewpettit]

If you truly run reloads daily and the max() works why not do max()-1 for the previous day?

[#4]

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,

[#5]

Hi Andrew,

i tried it with LOAD max(DateFile)-1 but it did not works.

Thank you,

Best Regards

[andrewpettit]

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.

[swuehl]

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?

[#8]

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 !

[swuehl]

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:

Load file based on timestamp

[#10 · Accepted solution]

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.