Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
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
Highlighted
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);

Partner
Partner

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.