Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
Not applicable

SQL Data change

Hello together,

I load from my SQL Server to rows "Datum" and "Uhrzeit". Like this:

DatumUhrzeit
2016-04-28 00:00:00.0001899-12-30 04:00:00.000
2016-04-28 00:00:00.0001899-12-30 05:00:00.000
2016-04-28 00:00:00.0001899-12-30 06:00:00.000
2016-04-28 00:00:00.0001899-12-30 06:19:05.000
2016-04-28 00:00:00.0001899-12-30 07:00:00.000
2016-04-28 00:00:00.0001899-12-30 08:00:00.000
2016-04-28 00:00:00.0001899-12-30 09:00:00.000
2016-04-28 00:00:00.0001899-12-30 10:00:00.000

From the row "Datum" I just need the 2016-04-28

and from the row "Uhrzeit" I need the 04:00:00.000 and 05:00:00.000 and so on

Like this:

DatumUhrzeit
2016-04-28 04:00:00.000
2016-04-28 05:00:00.000
2016-04-28 06:00:00.000
2016-04-28 06:19:05.000
2016-04-28 07:00:00.000
2016-04-28 08:00:00.000
2016-04-28 09:00:00.000
2016-04-28 10:00:00.000

How I can realized this?

4 Replies
gompa786
Contributor III

Re: SQL Data change

Hi..

Use MID function as below:

MID(Datum,1,10)&MID(Uhrzeit,12,12) as DatumUhrzeit

rgvavihs
Valued Contributor

Re: SQL Data change

Do you want to combine both fields then

try this in the script like this

load

Date(timestamp(Datum,'YYYY-MM-DD') &'  '& Time(Timestamp(Uhrzeit),'hh:mm:ss:sss') as DatumUhrzeit

from your table;

zhadrakas
Valued Contributor

Re: SQL Data change

Maybe like this

SourceData:
Load
Timestamp(Date#(date(Datum)) + time#(time(Uhrzeit)), 'DD.MM.YYYY hh:mm:ss.fff') as TEST
;
//YOUR SQL DATA
Load Timestamp('2016-04-28 00:00:00.000', 'DD.MM.YYYY hh:mm:ss.fff') as Datum,
Timestamp('1899-12-30 04:00:00.000', 'DD.MM.YYYY hh:mm:ss.fff') as Uhrzeit
autogenerate(1);

TKendrick20
Valued Contributor

Re: SQL Data change

This solution is long, but I think it will work with a wide variety of data types in your columns:

SELECT CONVERT(Date,CAST(Datum as DateTime)) + CONVERT(Datetime,CAST(Uhrzeit as Time))

Though it might be better to just fix this in your original data source.