Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

full load only if the file is changed..

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???

9 Replies
Highlighted
Specialist III
Specialist III

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

HI ,

Add another condition to check

if  $(VLastModifiedDate)=$(vActualDate)  OR

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

Highlighted
Specialist
Specialist

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

Hi

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

Highlighted
Champion II
Champion II

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

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

V

Highlighted
Creator
Creator

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

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:

Highlighted

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

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?

Highlighted
Creator
Creator

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

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

Highlighted

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

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

Highlighted

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

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.

Highlighted
Creator
Creator

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

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?