Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Hi
By looking at your data, date seems to be perfect.
Try not using date#. instead use Date function.
Can you please post sample data
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 |
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.
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
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]
Hi
By looking at your data, date seems to be perfect.
Try not using date#. instead use Date function.
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
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
Thanks for the help, the Date function worked.
Best regards
Des