Date in timestampm format

[#1]

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

[PrashantSangle]

Hi,

use floor() and Date() combination

try like

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

Regards

[PrashantSangle]

Hi,

You can also convert it into num then compare

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

Regards

[#4]

Hi,

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

Regards

[#5]

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

[ToniKautto]

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.

[MarcoWedel]

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

[pratap6699]

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