This timestamp field is strange

[n1ef5ng1]

Hi everyone,

Please see attached to understand more about this data column call Date. I am unable to change anything from data point of view as it is scrap totally from website.

Date as Timestamp,

     Date(floor(timestamp#(Date,'MM/DD/YYYY hh:mm')),'DD/MM/YYYY') as Date,

    hour(Timestamp#(Date,'MM/DD/YYYY hh:mm')) as Hour

Basically the raw data is timestamp,

however for conversion of timestamp to date and hour, I somehow cant get the first twelve days of the month for this timestamp. Anybody can help me on this? thanks in advance

[tresesco]

Try like:

LOAD

      Date as Timestamp,

      Date(floor(Alt(Date,timestamp#(Date,'MM/DD/YYYY hh:mm'))),'DD/MM/YYYY') as Date,

      hour(Alt(Date,Timestamp#(Date,'MM/DD/YYYY hh:mm'))) as Hour

[ganeshsvm]

Hi Ben,

Can you provide some sample data, your qvw has only script.

we need your data format to provide solution.

Regards,

Ganesh

[MindaugasBacius]

I would do as tresesco suggests but also will reduce data in Hour field like:

LOAD @1 as [Original Date]

       ,Date(Floor(Alt(@1,timestamp#(@1,'MM/DD/YYYY hh:mm'))),'DD/MM/YYYY') as Date

       ,hour(Floor(Alt(@1,Timestamp#(@1,'MM/DD/YYYY hh:mm')), 1/24)) as Hour

FROM

(biff, no labels, table is Sheet1$);

[n1ef5ng1]

Attached is the dataset, thanks

[tresesco]

PFA

[tresesco]

Hi,

     Hour() is not a formatting function. It actually returns an integer that represents hour. Hence the flooring would not be required here.

[n1ef5ng1]

Second of Jan to 12 of jan doesnt work. this is whole of Jan data. somehow it shows feb 01 mar 01 instead of jan 2nd and jan 3rd

[tresesco]

I don't get anything such in your sample data.

[n1ef5ng1]

I think   Qlikview is confuse with DD/MM and MM/DD on the first 12 days where they assume is month. see screenshot below. all these dates are Janaury so expected output shoulld be Jan 1st to Jan 31st