Need to convert Number into Date while loading from database(Sql Select in Qlikview)

[Anonymous]

Hi,

I am working on implementing a incremental load and I am stuck at a point where I need your help.

Date Format:1508818865

To convert this into proper date format, I am using below script at expression/Script level

Year(DATE(Floor(Num(DATE(Timestamp(Timestamp('1970-01-01 00:00:00.000') +  1508818865/60/60/24))))))

However this is throwing error when I am trying to restrict year while writing SQL Select statement in Qlikview in where condition.

eg: SQL SELECT  COL1,COL2,COL3,COL4 from table where

Year(DATE(Floor(Num(DATE(Timestamp(Timestamp('1970-01-01 00:00:00.000') +  1508818865/60/60/24))))))>='2015'

Please let me know how to proceed on this. Its a bit urgent.

Thanks

[swuehl]

You can only use functions supported by your DBMS when executing a SQL SELECT query.

You can use Qlik functions in a preceding LOAD though.

Preceding Load

But this will not limit the query result on the DBMS level, so it's better to use a WHERE clause with proper SQL functions to retrieve the year.

[Anonymous]

Hi Stefan,

Thanks for your reply.

Can you please help me with the code.

Appreciate your help.

Thanks

[wdchristensen]

What type of database are you pulling (Oracle, SQL Server)?

Date Format:1508818865

What date format is that in MM/DD/YYYY?

Might try to remove the quotes from 2015 since the year function returns an integer.

Year(DATE(Floor(Num(DATE(Timestamp(Timestamp('1970-01-01 00:00:00.000') +  1508818865/60/60/24))))))>=2015

[wdchristensen]

Or maybe

SELECT  COL1,COL2,COL3,COL4 from table

where

DATE(Floor(Num(DATE(Timestamp(Timestamp('1970-01-01 00:00:00.000') +  1508818865/60/60/24))))))>='01/01/2015'

[swuehl]

As said, it will depend on your database and SQL dialect.

For example, using MySQL, it may look like

SQL SELECT  COL1,COL2,COL3,COL4

FROM Table

WHERE

Year(from_unixtime(YourTimestampFieldShowingUnixTime)) >= 2015;

If in doubt, ask your local DB guru next door.

Regards,

Stefan

[Anonymous]

Hi Christensen,

I am pulling data from oracle DB.

Date Format:1508818865

When we use the query I mentioned in Qlikview, it converts to YYYY/MM/DD HH:MM:SS

[wdchristensen]

What is the error? Did solution without the year function generate the same error?

[swuehl]

Maybe something like

SQL SELECT  COL1,COL2,COL3,COL4

FROM Table

WHERE

Year( to_date('1970-01-01','YYYY-MM-DD') + numtodsinterval(YourTimestampFieldShowingUnixTime,'SECOND'))>=2015;