Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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???
HI ,
Add another condition to check
if $(VLastModifiedDate)=$(vActualDate) OR
($(VLastModifiedDate)>=Timestamp(date($(vActualDate) -1)+12/24)) and $(VLastModifiedDate)<=$(vActualDate))
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
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
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:
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?
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
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
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.
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?