topic Re: full load only if the file is changed.. in QlikView

full load only if the file is changed..

gauthamchilled — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

Our dashboard is scheduled to run 12 am and 12 pm.

I have a requirement to reload the file only if is modified in a day. My code is below

File:

Load Date(filetime()) as ModifiedDate

FROM

[..\Config\SectionAccessFile.xlsx]

(ooxml, embedded labels, table is Sheet1);

Let vActualDate =Today();

Let VLastModifiedDate = peek ( 'ModifiedDate', 0 , ' tmp ');

if $(VLastModifiedDate)=$(vActualDate) Then

//Full load happens below

Load * from Fulltable

ELSE

// QVD loads happen below

Load * from QVDtable

End if

The problem i face is, if someone modifies the file after 12 PM, the dashboard is not doing the full load. The issue here is the date changes at 12 AM, so the condition if $(VLastModifiedDate)=$(vActualDate) fails and picks up the QVD.

Anyone give me suggestion, how to rectifies this???

Re: full load only if the file is changed..

sasikanth — Fri, 17 Feb 2017 07:30:11 GMT

HI ,

Add another condition to check

if $(VLastModifiedDate)=$(vActualDate) OR

($(VLastModifiedDate)>=Timestamp(date($(vActualDate) -1)+12/24)) and $(VLastModifiedDate)<=$(vActualDate))

Re: full load only if the file is changed..

prat1507 — Fri, 17 Feb 2017 07:32:21 GMT

Do as below:

Let v_Now=Now()

Let vActualDate =Today();

Let VLastModifiedDate = peek ( 'ModifiedDate', 0 , ' tmp ');

if (Time(Now())>'0:00:00' and $(VLastModifiedDate)=$(vActualDate)) or (Time(Now())>'12:00:00' and $(VLastModifiedDate)=$(=date(vActualDate-1))) Then

//Full load happens below

Load * from Fulltable

ELSE

// QVD loads happen below

Load * from QVDtable

End if

Regards

Pratyush

Re: full load only if the file is changed..

vinieme12 — Fri, 17 Feb 2017 09:02:49 GMT

instead of comparing with actual today() date can I suggest you create a QVD to store the modified time in a separate one record qvd ?

For example :

1)your scheduled reload occurs at 12 am \

after all load is complete create a qvd as below

FileModifiedTime:

Load Date(filetime()) as ModifiedDate

Autogenerate(1);

Store FileModifiedTime into FileModifiedTime.QVD

2) You scheduled load is triggered at 12 pm

FileModifiedTime:

Load

ModifiedDate

FROM FileModifiedTime.QVD

if(Date(filetime()) > peek( 'FileModifiedTime',0,'ModifiedDate') ) Then Reload Occurs else Exit Script

We are just checking if the last modified time is less than current modified time

Makes Sense??

Cheers

Re: full load only if the file is changed..

gauthamchilled — Sat, 18 Feb 2017 13:23:14 GMT

hi Sasi,

it throwed me the following error

following is from document log

18-02-17 7:19:58 AM: 1002 Let vActualDate =Today()

18-02-17 7:19:58 AM: 1004 Let VLastModifiedDate = peek ( 'ModifiedDate', 0 , ' tmp ')

18-02-17 7:19:58 AM: 1007 If (18-02-17=18-02-17) OR

18-02-17 7:19:58 AM: Error: Script line error:

Re: full load only if the file is changed..

Peter_Cammaert — Sat, 18 Feb 2017 14:05:17 GMT

This probably isn't the cause of your Script Line Error but you should put all $-sign expansions of date variables between single quotes. Or QlikView will treat them as subtractions.

Can you post the entire IF statement that starts on line 1007?

Re: full load only if the file is changed..

gauthamchilled — Sat, 18 Feb 2017 14:20:29 GMT

Scrip statment:

Let vActualDate =Today();

Let VLastModifiedDate = peek ( 'ModifiedDate', 0 , ' tmp ');

If ($(VLastModifiedDate)=$(vActualDate)) // OR

If(($(VLastModifiedDate)>=Timestamp(date($(vActualDate) -1)+12/24)) and $(VLastModifiedDate)<=$(vActualDate)) Then

.......

Error:

18-02-17 8:18:13 AM: 1002 Let vActualDate =Today()

18-02-17 8:18:13 AM: 1004 Let VLastModifiedDate = peek ( 'ModifiedDate', 0 , ' tmp ')

18-02-17 8:18:13 AM: 1007 If (18-02-17=18-02-17)

18-02-17 8:18:13 AM: Error: Script line error:

18-02-17 8:18:22 AM: 1008 If((18-02-17>=Timestamp(date(18-02-17 -1)+12/24)) and 18-02-17<=18-02-17) Then

Re: full load only if the file is changed..

Peter_Cammaert — Sat, 18 Feb 2017 17:20:24 GMT

The IF control statement syntax is like: IF condition THEN stmt(s)[ELSE stmt(s) ] END IF

If you put the OR operator in comments and add a new IF in front of the next line, the first IF is missing a THEN keyword. That's why you get the "Syntax line error".

Best,

Peter

Re: full load only if the file is changed..

Peter_Cammaert — Sat, 18 Feb 2017 17:24:50 GMT

Also make sure that if you split one IF in two, to add another END IF somewhere at the end of the code block. Otherwise you'll get more syntax errors because of unterminated IF statements.

Re: full load only if the file is changed..

gauthamchilled — Mon, 13 Mar 2017 05:50:07 GMT

Hi Vineeth,

Can you please explain little more clearer about the 1st and 2nd load statement. Both statement table name is same???

Do I need to use a load conditon for both the statments?