topic Re: How to get modification time of a table ?? in QlikView

How to get modification time of a table ??

nikhilgarg — Fri, 05 Sep 2014 10:33:19 GMT

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.

Re: How to get modification time of a table ??

marcus_sommer — Fri, 05 Sep 2014 11:26:47 GMT

You could use filetime() - see here a similar post: Optimization of time reload.

- Marcus

Re: How to get modification time of a table ??

nikhilgarg — Fri, 05 Sep 2014 11:49:22 GMT

Hey marcus ,

I can not find any fulltime().

Can you tell me where it is ??

OR if you can tell me how to use it and compare with last execution time ??

Please hlp.

Thanx.

Re: How to get modification time of a table ??

marcus_sommer — Fri, 05 Sep 2014 11:54:13 GMT

filetime() not fulltime ... you will find some explanations by the file-functions within the help.

- Marcus

Re: How to get modification time of a table ??

nikhilgarg — Fri, 05 Sep 2014 12:20:22 GMT

Hey,

I created a column using filetime as:

FileTime()as Modified_time;

Now i want to compare Modified_time with LAstExecTIme() , but Modified_time is not being shown as column.

My COde is :

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

Directory;

LOAD * , FileTime()as Modified_time

FROM

[Disaster_Data\Copy of Disaster.xlsx]

(ooxml, embedded labels, table is Disaster) where Modified_time >= $(LastExecTime); // Here Modified_time is showing error as not assumned to be a column.

Concatenate

LOAD Id,

StartDate,

EndDate,

Country,

Type,

Sub_Type,

Name,

Killed,

Injured,

Year

FROM

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

STORE Disaster INTO C:\Users\nikhil.garg\Desktop\disaster1.qvd;

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

WHat to do ??

Please help.

Thanx.

Re: Re: How to get modification time of a table ??

jvitantonio — Fri, 05 Sep 2014 12:37:19 GMT

Hi Nikhil,

It's not a column until you fully create it. Try this instead:

Directory;
LET vModifiedTime = FileTime(Disaster_Data\Copy of Disaster.xlsx);

LOAD *
FROM [Disaster_Data\Copy of Disaster.xlsx] (ooxml, embedded labels, table is Disaster)
where vModifiedTime >=$(LastExecTime)
;


//****REST OF YOUR SCRIPT***

Re: Re: How to get modification time of a table ??

nikhilgarg — Mon, 08 Sep 2014 04:50:43 GMT

HEy,

I tried the way u did but it is giving me error :

My script is :

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

Directory;

LET vModifiedTime = FileTime('C:\Users\nikhil.garg\Desktop\Disaster_Data\[Copy of Disaster.xlsx]');

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);

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

Error is :

Re: How to get modification time of a table ??

jvitantonio — Mon, 08 Sep 2014 11:09:04 GMT

Hi,

Please try this:

...

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

Add vModifiedTime between $( )

Re: How to get modification time of a table ??

nikhilgarg — Mon, 08 Sep 2014 11:26:18 GMT

Hey,

THanx . I added $() and now it works but now it is giving another error of garbage statement:

Please help.

Re: How to get modification time of a table ??

jvitantonio — Mon, 08 Sep 2014 11:59:05 GMT

I can't debug an app like this. Can you please attach your app and your sample xml so I can work on it? You can put only one like of data

Re: Re: How to get modification time of a table ??

nikhilgarg — Mon, 08 Sep 2014 12:12:00 GMT

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.

Re: Re: How to get modification time of a table ??

nikhilgarg — Mon, 08 Sep 2014 12:17:29 GMT

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.

Re: Re: Re: How to get modification time of a table ??

jvitantonio — Mon, 08 Sep 2014 12:37:05 GMT

Here's the modified app.

Re: How to get modification time of a table ??

nikhilgarg — Tue, 09 Sep 2014 04:37:47 GMT

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.

Re: How to get modification time of a table ??

PrashantSangle — Tue, 09 Sep 2014 04:56:24 GMT

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

Re: How to get modification time of a table ??

jvitantonio — Tue, 09 Sep 2014 08:35:46 GMT

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.

Re: How to get modification time of a table ??

nikhilgarg — Tue, 09 Sep 2014 09:47:41 GMT

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

Re: How to get modification time of a table ??

jvitantonio — Tue, 09 Sep 2014 10:50:42 GMT

Please refer to the help.

Re: How to get modification time of a table ??

nikhilgarg — Tue, 09 Sep 2014 11:55:03 GMT

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

Re: How to get modification time of a table ??

jvitantonio — Tue, 09 Sep 2014 12:48:08 GMT

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.