Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

LOAD Max(DateFile)

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,

1 Solution

Accepted Solutions
Not applicable

Re: LOAD Max(DateFile)

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.

9 Replies
Gysbert_Wassenaar
Not applicable

Re: LOAD Max(DateFile)

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


talk is cheap, supply exceeds demand
andrewpettit
Not applicable

Re: LOAD Max(DateFile)

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

Not applicable

Re: LOAD Max(DateFile)

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,

Not applicable

Re: LOAD Max(DateFile)

Hi Andrew,

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

Thank you,

Best Regards

andrewpettit
Not applicable

Re: LOAD Max(DateFile)

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
Not applicable

Re: LOAD Max(DateFile)

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?

Not applicable

Re: LOAD Max(DateFile)

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
Not applicable

Re: LOAD Max(DateFile)

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

Not applicable

Re: LOAD Max(DateFile)

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.