topic Re: LOAD Max(DateFile) in QlikView

LOAD Max(DateFile)

Wed, 30 Oct 2013 13:23:32 GMT

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,

Re: LOAD Max(DateFile)

Gysbert_Wassenaar — Wed, 30 Oct 2013 13:36:41 GMT

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

Re: LOAD Max(DateFile)

andrewpettit — Wed, 30 Oct 2013 13:38:40 GMT

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

Re: LOAD Max(DateFile)

Wed, 30 Oct 2013 13:54:29 GMT

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,

Re: LOAD Max(DateFile)

Wed, 30 Oct 2013 14:01:31 GMT

Hi Andrew,

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

Thank you,

Best Regards

Re: LOAD Max(DateFile)

andrewpettit — Wed, 30 Oct 2013 14:13:17 GMT

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.

Re: LOAD Max(DateFile)

swuehl — Wed, 30 Oct 2013 14:20:47 GMT

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?

Re: LOAD Max(DateFile)

Wed, 30 Oct 2013 15:18:54 GMT

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 !

Re: LOAD Max(DateFile)

swuehl — Wed, 30 Oct 2013 15:48:06 GMT

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

Re: LOAD Max(DateFile)

Fri, 01 Nov 2013 11:47:40 GMT

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.