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
I have not seen the field from this excel
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);
"Field names must be unique within table"
this is the error now.
How about 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 *
FROM
(ooxml, embedded labels);
Item code | Item name | Date timestamp | User | From | To | Quantity | Comments | From Container | To Container | Attribute 3 | Attribute 4 | Attribute 5 | Attribute 6 | |
139922 | FOX GLACIER MINTS 195G | '17/03/2017 15:02' | Malik | GI1B | 1932 | Stock registered | CON-7384 | 1 | FOOD VAT | 1 | SweetsBags-Confectionery | |||
12362 | CHUPA CHUPS 20 MINI LOLLIES | '09/02/2017 17:40' | AUGUSTO | GI1B | 1944 | Stock registered | CON-5647 | 1 | FOOD VAT | 1 | SweetsBags-Confectionery |
I need [Item code] as REF
This worked for me
Table:
LOAD *,
Year(PVX_DATE) as PVX_YEAR,
Month(PVX_DATE)as PVX_MONTH,
Day(PVX_DATE) as PVX_DAY;
LOAD *,
[Item code] as REF,
Date(Floor(TimeStamp#(PurgeChar([Date timestamp], Chr(39)), 'DD/MM/YYYY hh:mm'))) as PVX_DATE;
LOAD * INLINE [
Item code, Item name, Date timestamp, User, From, To, Quantity, Comments, From Container, To Container, Attribute 3, Attribute 4, Attribute 5, Attribute 6,
139922, FOX GLACIER MINTS 195G, "'17/03/2017 15:02'", Malik, , GI1B, 1932, Stock registered, , CON-7384, 1, FOOD VAT, 1, SweetsBags-Confectionery,
12362, CHUPA CHUPS 20 MINI LOLLIES, "'09/02/2017 17:40'", AUGUSTO, , GI1B, 1944, Stock registered, , CON-5647, 1, FOOD VAT, 1, SweetsBags-Confectionery,
];
Just replace inline with your Excel load here
Hi Everyone,
Thank you so much for trying really appreciate your time and effort:
Finally the following code has worked,
GI_PVX:
LOAD [Item code] As REF,
[Item name],
// date(Date#([Date timestamp],dd/mm/yyyy)),
Replace( PurgeChar([Date timestamp],chr(39) ),',',';') as PVX_DATE,
Year(TimeStamp#(Replace( PurgeChar([Date timestamp],chr(39) ),',',';'),'DD/MM/YYYY hh:mm')) As PVX_YEAR,
Month(TimeStamp#(Replace( PurgeChar([Date timestamp],chr(39) ),',',';'),'DD/MM/YYYY hh:mm')) As PVX_MONTH,
Day(TimeStamp#(Replace( PurgeChar([Date timestamp],chr(39) ),',',';'),'DD/MM/YYYY hh:mm')) As PVX_DAY,
User,
From,
To,
Quantity,
Comments,
[From Container],
[To Container],
[Attribute 3],
[Attribute 4],
[Attribute 5],
[Attribute 6]
FROM
(ooxml, embedded labels);
I took this code =Replace( PurgeChar( vVariable ,chr(39) ),',',';') from stephan1983.
Regards,
M Talal