topic Re: Timestamp has got single courses ' ' in QlikView

Timestamp has got single courses ' '

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

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

Re: Timestamp has got single courses ' '

aarkay29 — Mon, 03 Apr 2017 14:04:29 GMT

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);

Re: Timestamp has got single courses ' '

Anil_Babu_Samineni — Mon, 03 Apr 2017 14:05:03 GMT

How was the [Date timestamp] Date values looking?

Re: Timestamp has got single courses ' '

Anonymous — Mon, 03 Apr 2017 14:56:56 GMT

Hi Anil,

'01/04/2017 10:51'

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

Regards,

M Talal

Re: Timestamp has got single courses ' '

Anonymous — Mon, 03 Apr 2017 15:10:33 GMT

Hi Aar,

I tried it its not working.

Thanks.

M Talal

Re: Timestamp has got single courses ' '

aarkay29 — Mon, 03 Apr 2017 15:12:29 GMT

Can you please tell me

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

Re: Timestamp has got single courses ' '

Anonymous — Mon, 03 Apr 2017 15:21:58 GMT

Hi,

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

may be a format problem somewhere.

Re: Timestamp has got single courses ' '

sunny_talwar — Mon, 03 Apr 2017 15:25:33 GMT

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);

Re: Timestamp has got single courses ' '

aarkay29 — Mon, 03 Apr 2017 15:27:05 GMT

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);

Re: Timestamp has got single courses ' '

sunny_talwar — Mon, 03 Apr 2017 15:27:58 GMT

Sample attached

Re: Timestamp has got single courses ' '

Anonymous — Mon, 03 Apr 2017 15:55:21 GMT

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.

Re: Timestamp has got single courses ' '

sunny_talwar — Mon, 03 Apr 2017 15:58:32 GMT

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

Re: Timestamp has got single courses ' '

Anonymous — Mon, 03 Apr 2017 16:09:50 GMT

Hi Sunny,

I did that but now its showing this error, What can be the problem now.

Re: Timestamp has got single courses ' '

sunny_talwar — Mon, 03 Apr 2017 16:11:44 GMT

May be get rid of this * from your first load

Re: Timestamp has got single courses ' '

Anonymous — Mon, 03 Apr 2017 16:15:34 GMT

I removed that now its giving errror "Field not found - <Date timestamp>"

Re: Timestamp has got single courses ' '

sunny_talwar — Mon, 03 Apr 2017 16:16:33 GMT

Can you post your script as text rather than image?

Re: Timestamp has got single courses ' '

Anonymous — Mon, 03 Apr 2017 16:18:43 GMT

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);

Re: Timestamp has got single courses ' '

sunny_talwar — Mon, 03 Apr 2017 16:19:47 GMT

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);

Re: Timestamp has got single courses ' '

Anil_Babu_Samineni — Mon, 03 Apr 2017 16:22:19 GMT

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);

Re: Timestamp has got single courses ' '

Anonymous — Mon, 03 Apr 2017 16:32:12 GMT

Hi its is coming from this xlsx file