How to get modification time of a table ??

[nikhilgarg]

Hey,

TO use incremental load for newly added rows how can i get modification time of table ?? So that i can compare it with LAst Execution Time of table and get the updated results or newly added rows  only.

Please help.

Thanx.

[nikhilgarg]

Hey,

I have attached the .qvw and excel file. Please see to it.

And also see to the path i have used for this .qvw.

Thanks.

[nikhilgarg]

Hey,

I have attached the .qvw and excel file. Please see to it.

And also see to the path i have used for this .qvw.

PLEASE open the app with QLIKVIEW and not with QLIKSENSE

Thanks.

[jvitantonio]

Here's the modified app.

[nikhilgarg]

Hey,

I can not open this document in my qlikview desktop. It is showing some personal edition issue.

So can you show me the script that you changed ??

Please help.

Thanks.

[PrashantSangle]

Hi,

If see your error you have date format issue.

Modify your one of the date field to compare with each other and if possible

Make it to rounded date using floor()

Your ModifyTime Date Format is 'MM/DD/YYYY hh:mm:ss TT'

And Last Execution Time Format is  'YYYY-MM-DD hh:mm:ss'

You have to round it and then try.

Regards

[jvitantonio]

Hi,

Here's the script. I've changed the path to be in the same directory. You can change it if you need to.

//******************************************************//

Directory;

LET vModifiedTime = date(floor(FileTime('Copy of Disaster.xlsx')), 'DD-MM-YYYY') ;

trace Last modified date: $(vModifiedTime);

LOAD *

WHERE StartDate >= DATE('$(vModifiedTime)','DD-MM-YYYY hh:mm:ss');

LOAD Id,

     alt( date#( StartDate , 'YYYY-MM-DD' ),

  date#( StartDate , 'DD-MM-YYYY' ),

  date#( StartDate , 'DD-MM-YY' ),

  'No valid date' ) as StartDate,

     EndDate,

     Country,

     Type,

     Sub_Type,

     Name,

     Killed,

     Injured,

     Year

FROM

[Copy of Disaster.xlsx]

(ooxml, embedded labels, table is Disaster)

;

//**************END OF SCRIPT *****************///

The above is the script that covers what you needed. you can then do you own modification, like storing the table, etc.

JV

[nikhilgarg]

Hey,

Sorry but i cannot understand this script.

LET vModifiedTime = date(floor(FileTime('Copy of Disaster.xlsx')), 'DD-MM-YYYY') ;

trace Last modified date: $(vModifiedTime);

Why date() and floor () is used ??

Why trace() is used ??

Why thje following is used:

alt( date#( StartDate , 'YYYY-MM-DD' ),

  date#( StartDate , 'DD-MM-YYYY' ),

  date#( StartDate , 'DD-MM-YY' ),

  'No valid date' ) as StartDate,

WHy there is  no concatenate ??

Please help in understanding.

Thanks

[jvitantonio]

Please refer to the help.

JV

[nikhilgarg]

Hey,

I  dont understand why u r comparing it with StartDate.

I have to compare Modified Timeof file with LAst Exceution TIme of file.

I have used it like this:

----------------------------------------------------------------

Directory;

LET vModifiedTime = date(floor(FileTime('C:\Users\nikhil.garg\Desktop\Disaster_Data\Copy of Disaster.xlsx')), 'YYYY-MM-DD') ;

LOAD *

FROM

[Disaster_Data\Copy of Disaster.xlsx]

(ooxml, embedded labels, table is Disaster) where $(vModifiedTime) >= $(LastExecTime);

Concatenate

LOAD Id,

     StartDate,

     EndDate,

     Country,

     Type,

     Sub_Type,

     Name,

     Killed,

     Injured,

     Year

FROM

disaster1.qvd (qvd) WHERE NOT Exists(Id);

-----------------------------------------------------------------------

The problem is  the value of vModifiedTime is not comparable with LAstExecutionTime.

vModifiedTime is giving me date only while LAstExecutionTime giving me time along with date.

What should i do to get time along with date in vModifiedDate.

Please help.

Thanks

[jvitantonio]

Hi,

This is not the right approach.

I compared it with Start date because you need a field in your table to compare LastExec date.

In your example, you need to add one more field to your table (ModifiedDate) and use my script replacing StartDate with your new field Modified Date.

JV