Solved: Timestamp has got single courses ' ' - Qlik Community

Anonymous · ‎2017-04-03

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

Anonymous · ‎2017-04-04

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

View solution in original post

aarkay29 · ‎2017-04-03

Try This

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;

Load

[Item code],

[Item name],

Replace([Date timestamp],''','') as Date timestamp

FROM

(ooxml, embedded labels);

Anil_Babu_Samineni · ‎2017-04-03

How was the [Date timestamp] Date values looking?

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

Anonymous · ‎2017-04-03

Hi Anil,

'01/04/2017 10:51'

as you can see in the snap shot of ListBox in highlighted green color.

Regards,

M Talal

Anonymous · ‎2017-04-03

Hi Aar,

I tried it its not working.

Thanks.

M Talal

aarkay29 · ‎2017-04-03

Can you please tell me

when you try above code what is it giving you ??

Anonymous · ‎2017-04-03

Hi,

it shows no result after load and all the tables below this code does not load either.

may be a format problem somewhere.

sunny_talwar · ‎2017-04-03

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 [Item code] As REF,

[Item name],

Date(Floor(TimeStamp#(PurgeChar([Date timestamp], Chr(39)), 'DD/MM/YYYY hh:mm'))) as PVX_DATE

FROM

(ooxml, embedded labels);

aarkay29 · ‎2017-04-03

Sorry about that

Can you try like these and check

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;

Load

[Item code],

[Item name],

Replace([Date timestamp],chr(39),'') as [Date timestamp]

FROM

(ooxml, embedded labels);

sunny_talwar · ‎2017-04-03

Sample attached