Direct discovery - time data type. PROBLEM!

[igor-st80]

Hello,

My problem - Direct discovery and time data type

Please help.

Source MS Sql Server 2008 R2

Table UsersLog whith field lgdate - data type "Datetime"

connect oledb

DIRECT QUERY

DIMENSION

  userLogin as "Login"

DETACH

  NATIVE('CONVERT(varchar(10), lgdate,102)') as "Date",

  NATIVE('rtrim(ltrim(str(datepart(hh, lgdate))))+char(45)+rtrim(ltrim(str( datepart(mi, lgdate))))+char(45)+rtrim(ltrim(str(datepart(s, lgdate))))') as "Time 1",

  NATIVE('replace(CONVERT(varchar(8), lgdate,108),'':'',''|'')') as "Time 2",

  NATIVE('CONVERT(varchar(19), lgdate)') as "DateTime 1",

  NATIVE('CONVERT(varchar(19),lgdate,120)') as "DateTime 2",

  lgdate

from UsersLog

Result after LOAD:

Date	DateTime 1	DateTime 2	lgdate	Login	Time 1	Time 2
2014.09.04	Sep  4 2014 12:25PM	2014-09-04 12:25:42	04.09.2014 12:25	zzz	12-25-42	12|25|42
2014.09.04	Sep  4 2014 12:47PM	2014-09-04 12:47:00	04.09.2014 12:47	xxx	12-47-0	12|47|00
2014.09.04	Sep  4 2014 12:53PM	2014-09-04 12:53:19	04.09.2014 12:53	ccc	12-53-19	12|53|19

All good!!!

Next, add new record in source table and result QV:

Date	DateTime 1	DateTime 2	lgdate	Login	Time 1	Time 2
2014.09.04	Sep  4 2014 12:25PM	2014-09-04 12:25:42	04.09.2014 12:25	zzz	12-25-42	12|25|42
2014.09.04	Sep  4 2014 12:47PM	2014-09-04 12:47:00	04.09.2014 12:47	xxx	12-47-0	12|47|00
2014.09.04	Sep  4 2014 12:53PM	2014-09-04 12:53:19	04.09.2014 12:53	ccc	12-53-19	12|53|19
2014.09.04				vvv

Where is Time new record? what is problem?

Thanks

[igor-st80 · Accepted solution]

QlikView Direct Discovery FAQ 11.2 SR7.pdf

• Can be used to build aggregated charts on homogeneous large data sets

• Can be used to look at detail records in a table box on large data sets

• Can reflect updated records without reloads (not new records)

• Can support more than one Direct Discovery table in certain scenarios

• Not as fast as in memory apps

• Will always be slower compared to SQL query run times on source due to associative model

calculation times

• Not a solution for scalability/performance issues in the underlying source

• Not designed to convert all tables in apps into Direct Discovery mode

• Not a real time solution