full load only if the file is changed..

[gauthamchilled]

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

[sasikanth]

HI ,

Add another condition to check

if  $(VLastModifiedDate)=$(vActualDate)  OR

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

[prat1507]

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

[vinieme12]

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

[gauthamchilled]

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:

[Peter_Cammaert]

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?

[gauthamchilled]

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

[Peter_Cammaert]

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

[Peter_Cammaert]

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.

[gauthamchilled]

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?