Solved: Can somebody help with Time date? - Qlik Community

Anonymous · ‎2016-09-06

Hi

I want to get the date in year month day and am usint the following, the timestamp is in the [Expected start] column and i have formated it as Date in excel.

SET TimeFormat='hh:mm:ss';
SET DateFormat='YYYY-MM-DD';
SET TimestampFormat='YYYY-MM-DD hh:mm:ss[.fff]';
SET MonthNames='jan;feb;mar;apr;maj;jun;jul;aug;sep;okt;nov;dec';
SET DayNames='må;ti;on;to;fr;lö;sö';

LOAD *,
Month(Date) As Month,
Year(Date) As Year,
Day(Date) as Day;
LOAD Date(Date#([Expected start], 'YYYY-MM-DD')) As Date,
Name,
[Expected start],
Object,
[Job type],
Variant,
Trade,
[Forecast hours],
[Functional location],
[Customer account],
[Reference type],
[Reference ID],
Description,
Priority,
Criticality,
[Expected end],
Status,
[Work order],
[Work order pools]

I have tried to find solution through the forum but cannot get it to work. the feilds Year month and day are empty when i load the data.

saimahasan · ‎2016-09-06

Hi

By looking at your data, date seems to be perfect.

Try not using date#. instead use Date function.

View solution in original post

saimahasan · ‎2016-09-06

Can you please post sample data

Anonymous · ‎2016-09-06

The file is quite big but here so i will just copy in an example here

Hope this is ok

Expected start

Object

Job type

Variant

Trade

Forecast hours

Functional location

Customer account

Reference type

Reference ID

Description

Priority

Criticality

Expected end

Status

Work order

Work order pools

2017-07-01

OBJ00093

INSPECTION

0,00

DK-JYL-SYD

DK23476

Maintenance sequences

LM-1-SI

Safety Inspection

3

Created

2017-01-01

OBJ00093

SERVICE

0,00

DK-JYL-SYD

DK23476

Maintenance sequences

LM-2-PM

Half Yearly Service

3

Created

2017-07-01

OBJ00093

SERVICE

0,00

DK-JYL-SYD

DK23476

Maintenance sequences

LM-2-PM

Half Yearly Service

3

Created

2017-04-01

OBJ00088

INSPECTION

0,00

DK-SJÆ

DK23089

Maintenance sequences

LM-1-SI

Safety Inspection

3

Created

2016-10-01

OBJ00088

SERVICE

0,00

DK-SJÆ

DK23089

Maintenance sequences

LM-4-PM

Quarterly Service

3

Created

2017-01-01

OBJ00088

SERVICE

0,00

DK-SJÆ

DK23089

Maintenance sequences

LM-4-PM

Quarterly Service

3

Created

2017-04-01

OBJ00088

SERVICE

0,00

DK-SJÆ

DK23089

Maintenance sequences

LM-4-PM

Quarterly Service

3

Created

2017-07-01

OBJ00088

SERVICE

0,00

DK-SJÆ

DK23089

Maintenance sequences

LM-4-PM

Quarterly Service

3

Created

2017-01-01

OBJ00089

SERVICE

0,00

DK-JYL-MIDT

DK23089

Maintenance sequences

LM-3-PM

Service 3 times/Year

3

Created

2017-05-01

OBJ00089

SERVICE

0,00

DK-JYL-MIDT

DK23089

Maintenance sequences

LM-3-PM

Service 3 times/Year

3

Created

krishnacbe · ‎2016-09-06

Hi,

Check whether the field is Date Type from the data source. Might be in text format due to that you are not getting the values.

Anonymous · ‎2016-09-06

Hi

Thanks for the tip, i have read about that in a similar thread and set the format to Date in the original Excel file.

/des

Chanty4u · ‎2016-09-06

try below

LOAD *,
Name,
[Expected start],

Date(Date#([Expected start], 'YYYY-MM-DD'),'YYYY-MM-DD') As Date,

Month(Date) As Month,
Year(Date) As Year,
Day(Date) as Day

Object,
[Job type],
Variant,
Trade,
[Forecast hours],
[Functional location],
[Customer account],
[Reference type],
[Reference ID],
Description,
Priority,
Criticality,
[Expected end],
Status,
[Work order],
[Work order pools]

saimahasan · ‎2016-09-06

Hi

By looking at your data, date seems to be perfect.

Try not using date#. instead use Date function.

kaushiknsolanki · ‎2016-09-06

Hi,

Just like Saima Suggested you can try this.

LOAD *,
Month(Date) As Month,
Year(Date) As Year,
Day(Date) as Day;
LOAD Date([Expected start],'YYYY-MM-DD') As Date,
Name,
[Expected start],
Object,
[Job type],
Variant,
Trade,
[Forecast hours],
[Functional location],
[Customer account],
[Reference type],
[Reference ID],
Description,
Priority,
Criticality,
[Expected end],
Status,
[Work order],
[Work order pools]

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!

HirisH_V7 · ‎2016-09-06

Hi,

Check this,

LOAD Date([Expected start],'YYYY-MM-DD') As Date,
Month(Date([Expected start],'YYYY-MM-DD')) as Month,
Year(Date([Expected start],'YYYY-MM-DD')) as Year,
Day(Date([Expected start],'YYYY-MM-DD')) as Day,
Name,
[Expected start], 
Object, 
[Job type], 
Variant, 
Trade, 
[Forecast hours], 
[Functional location], 
[Customer account], 
[Reference type], 
[Reference ID], 
Description, 
Priority, 
Criticality, 
[Expected end], 
Status, 
[Work order], 
[Work order pools]

HTH,

Hirish

HirisH
“Aspire to Inspire before we Expire!”

Anonymous · ‎2016-09-06

Thanks for the help, the Date function worked.

Best regards

Des