topic Re: Date in timestampm format in QlikView

Date in timestampm format

Sat, 06 Jun 2015 06:43:27 GMT

How to do the comparison when date present in timestamp format ,

To fetch all the date greater than 31-03-2015

date

01-03-2012 16:45

01-03-2012 21:30:00

22-05-2014 05:30:09

30-03-2015 16:45

30-03-2015 12:00:00

30-03-2015 09:30:01

31-03-2015 16:45

31-03-2015 21:06:09

31-03-2015 10:29:00

Re: Date in timestampm format

PrashantSangle — Sat, 06 Jun 2015 06:47:51 GMT

Hi,

use floor() and Date() combination

try like

date(floor(youdatefield))>31-03-2015

Regards

Re: Date in timestampm format

PrashantSangle — Sat, 06 Jun 2015 06:49:49 GMT

Hi,

You can also convert it into num then compare

like Num(YourdateField)>num('31-03-2015')

Regards

Re: Date in timestampm format

Sat, 06 Jun 2015 07:16:13 GMT

Hi,

Year(YourDateField) >=2015 and Month(YourDateField) >3

Regards

Re: Date in timestampm format

Sat, 06 Jun 2015 07:43:04 GMT

Hi,

you can try this,

LOAD * INLINE [

date

01-03-2012 16:45

01-03-2012 21:30:00

22-05-2014 05:30:09

30-03-2015 16:45

30-03-2015 12:00:00

30-03-2015 09:30:01

31-03-2015 16:45

31-03-2015 21:06:09

31-03-2015 10:29:00

]

Where date > '31-03-2015';

Re: Date in timestampm format

ToniKautto — Sat, 06 Jun 2015 09:26:36 GMT

If you are loading from a SQL source, I would suggest filtering the data in the SQL query. This way you load an optimized amount of data from the source to QlikView, this will minimize load on network and data source.

From a data loading perspective the key thing is that you format your data properly, so that a timestamp is a timestamp and a date is a date. The key thing in this context is to notice that a timestamp is a decimal value and that a date is a integer, when it comes to the underlying numerical representation.

In your sample data you have two different incoming formats, so you need to handle both options to unify your data format. In the below example this is done by using the Alt() function. Notice that this assumes that you have one of the formats as your defined in your format variables.

SET TimeFormat='hh:mm:ss';
SET DateFormat='DD-MM-YYYY';
SET TimestampFormat='DD-MM-YYYY hh:mm:ss';

LOAD
   SourceTimestamp,
   Alt( Timestamp#(SourceTimestamp),
        Timestamp(Timestamp#(SourceTimestamp, 'DD-MM-YYYY hh:mm')) ) AS MyTimestamp
Inline [
SourceTimestamp
01-03-2012 16:45
01-03-2012 21:30:00
22-05-2014 05:30:09
30-03-2015 12:00:00
30-03-2015 16:45
31-03-2015 16:45
30-03-2015 09:30:01
31-03-2015 21:06:09
31-03-2015 10:29:00
];

On top of the initial load you can then add a load statement that splits the timestamp into proper date and time values. A date is an integer, so you need to use Floor() to remove the decimal component form the timestamp. A time value is a decimal value with a zro integer, which you can accomplish by using the Frac() function.

LOAD 
   SourceTimestamp,
   MyTimestamp,
   Date(Floor(MyTimestamp)) AS MyDate,
   Time(Frac(MyTimestamp)) AS MyTime
;

At this point you have the required values to do your filtering, so in the final load you can now adda where cause that excludes the dates that you are not interested in.

LOAD *
Where MyDate > '30-03-2015';

See attached QVW for a full example. Comment out the load statement to evaluate what happens in each step.

Re: Date in timestampm format

MarcoWedel — Sat, 06 Jun 2015 10:41:08 GMT

Hi,

another solution could be:

SET TimeFormat='hh:mm:ss';
SET DateFormat='MM/DD/YYYY';
SET TimestampFormat='MM/DD/YYYY hh:mm:ss[.fff]';

tabDates:
LOAD SourceTimestamp,
     DayName(SourceTimestamp) as SourceDate,
     Time(fmod(SourceTimestamp,1)) as SourceTime
Where SourceTimestamp > '03/31/2015';
LOAD Timestamp(Alt(Timestamp#(SourceTimestamp,'DD-MM-YYYY hh:mm'),Timestamp#(SourceTimestamp,'DD-MM-YYYY hh:mm:ss'))) as SourceTimestamp
FROM [https://community.qlik.com/thread/167228] (html, codepage is 1252, embedded labels, table is @1, filters(ColSplit(1, IntArray()),Remove(Row, Pos(Top, 10)),Remove(Row, Pos(Top, 9)),Remove(Row, Pos(Top, 8)),Remove(Row, Pos(Top, 7)),Remove(Row, Pos(Top, 6)),Remove(Row, Pos(Top, 5)),Remove(Row, Pos(Top, 4)),Remove(Row, Pos(Top, 3)),Remove(Row, Pos(Top, 2)),Remove(Row, Pos(Top, 1)),Remove(Row, Pos(Top, 11)),Remove(Row, Pos(Top, 11))));

hope this helps

regards

Marco

Re: Date in timestampm format

pratap6699 — Sat, 06 Jun 2015 10:41:44 GMT

LOAD * INLINE [

date

01-03-2012 16:45

01-03-2012 21:30:00

22-05-2014 05:30:09

30-03-2015 16:45

30-03-2015 12:00:00

30-03-2015 09:30:01

31-03-2015 16:45

31-03-2015 21:06:09

31-03-2015 10:29:00

]

Where date(date,'DD/MM/YY') > '31-03-2015';