Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Everyone,
I am trying to use a timestamp and trying to extract the month, year and day but for some reason, I am not successful
one thing I have noticed is the Time stamp is showing commas in the text field
this is the following code i am using
GI_PVX:
LOAD [Item code] As REF,
[Item name],
[Date timestamp] as PVX_DATE,
Year(TimeStamp#([Date timestamp],'DD/MM/YYYY hh:mm')) As PVX_YEAR,
Month(TimeStamp#([Date timestamp],'DD/MM/YYYY hh:mm')) As PVX_MONTH,
Day(TimeStamp#([Date timestamp],'DD/MM/YYYY hh:mm')) As PVX_DAY,
FROM
(ooxml, embedded labels);
Please help me on this.
Regards,
M Talal
Hi Sunny,
Thank you so much, Its seem we are ver close,I am doing this and getting some error when trying to reload.
Please let me know where am I going wrong.
The order of the load is not right, Year, Month and Day needs to go above the place where you create the PVX_DATE field
Hi Sunny,
I did that but now its showing this error, What can be the problem now.
May be get rid of this * from your first load
I removed that now its giving errror "Field not found - <Date timestamp>"
Can you post your script as text rather than image?
GI_PVX:
LOAD *,
Year(PVX_DATE) as PVX_YEAR,
Month(PVX_DATE)as PVX_MONTH,
Day(PVX_DATE) as PVX_DAY;
LOAD *,
Date(Floor(TimeStamp#(PurgeChar([Date timestamp], Chr(39)), 'DD/MM/YYYY hh:mm'))) as PVX_DATE;
LOAD
[Item code] As REF,
[Item name],
User,
From,
To,
Quantity,
Comments,
[From Container],
[To Container],
[Attribute 3],
[Attribute 4],
[Attribute 5],
[Attribute 6]
FROM
(ooxml, embedded labels);
Try this:
GI_PVX:
LOAD *,
Year(PVX_DATE) as PVX_YEAR,
Month(PVX_DATE)as PVX_MONTH,
Day(PVX_DATE) as PVX_DAY;
LOAD *,
Date(Floor(TimeStamp#(PurgeChar([Date timestamp], Chr(39)), 'DD/MM/YYYY hh:mm'))) as PVX_DATE;
LOAD *,
[Item code] As REF
FROM
(ooxml, embedded labels);
Muhammad Talal Uddin wrote:
GI_PVX:
LOAD *,
Year(PVX_DATE) as PVX_YEAR,
Month(PVX_DATE)as PVX_MONTH,
Day(PVX_DATE) as PVX_DAY;
LOAD *,
Date(Floor(TimeStamp#(PurgeChar([Date timestamp], Chr(39)), 'DD/MM/YYYY hh:mm'))) as PVX_DATE;
LOAD
[Item code] As REF,
[Item name],
User,
From,
To,
Quantity,
Comments,
[From Container],
[To Container],
[Attribute 3],
[Attribute 4],
[Attribute 5],
[Attribute 6]
FROM
(ooxml, embedded labels);
From here, You don't have field [Date timestamp]. Where this field coming from.
LOAD *,
Date(Floor(TimeStamp#(PurgeChar([Date timestamp], Chr(39)), 'DD/MM/YYYY hh:mm'))) as PVX_DATE;
LOAD
[Item code] As REF,
[Item name],
User,
From,
To,
Quantity,
Comments,
[From Container],
[To Container],
[Attribute 3],
[Attribute 4],
[Attribute 5],
[Attribute 6]
FROM
(ooxml, embedded labels);
Hi its is coming from this xlsx file