topic Can somebody help with Time date? in QlikView

Can somebody help with Time date?

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Can somebody help with Time date?

saimahasan — Tue, 06 Sep 2016 17:12:04 GMT

Can you please post sample data

Re: Can somebody help with Time date?

Anonymous — Tue, 06 Sep 2016 17:16:52 GMT

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

Created

2017-01-01

OBJ00093

SERVICE

0,00

DK-JYL-SYD

DK23476

Maintenance sequences

LM-2-PM

Half Yearly Service

Created

2017-07-01

OBJ00093

SERVICE

0,00

DK-JYL-SYD

DK23476

Maintenance sequences

LM-2-PM

Half Yearly Service

Created

2017-04-01

OBJ00088

INSPECTION

0,00

DK-SJÆ

DK23089

Maintenance sequences

LM-1-SI

Safety Inspection

Created

2016-10-01

OBJ00088

SERVICE

0,00

DK-SJÆ

DK23089

Maintenance sequences

LM-4-PM

Quarterly Service

Created

2017-01-01

OBJ00088

SERVICE

0,00

DK-SJÆ

DK23089

Maintenance sequences

LM-4-PM

Quarterly Service

Created

2017-04-01

OBJ00088

SERVICE

0,00

DK-SJÆ

DK23089

Maintenance sequences

LM-4-PM

Quarterly Service

Created

2017-07-01

OBJ00088

SERVICE

0,00

DK-SJÆ

DK23089

Maintenance sequences

LM-4-PM

Quarterly Service

Created

2017-01-01

OBJ00089

SERVICE

0,00

DK-JYL-MIDT

DK23089

Maintenance sequences

LM-3-PM

Service 3 times/Year

Created

2017-05-01

OBJ00089

SERVICE

0,00

DK-JYL-MIDT

DK23089

Maintenance sequences

LM-3-PM

Service 3 times/Year

Created

Re: Can somebody help with Time date?

krishnacbe — Tue, 06 Sep 2016 17:20:26 GMT

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.

Re: Can somebody help with Time date?

Anonymous — Tue, 06 Sep 2016 17:22:53 GMT

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

Re: Can somebody help with Time date?

Chanty4u — Tue, 06 Sep 2016 17:23:58 GMT

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]

Re: Can somebody help with Time date?

saimahasan — Tue, 06 Sep 2016 17:25:35 GMT

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

Try not using date#. instead use Date function.

Re: Can somebody help with Time date?

kaushiknsolanki — Tue, 06 Sep 2016 17:32:47 GMT

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

Re: Can somebody help with Time date?

HirisH_V7 — Tue, 06 Sep 2016 17:51:56 GMT

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

Re: Can somebody help with Time date?

Anonymous — Tue, 06 Sep 2016 17:55:10 GMT

Thanks for the help, the Date function worked.

Best regards

Des