Skip to main content
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Get each date info from TimeStamp

Hello,

I have a column called "Fecha" in TimeStamp as the img below:

uz3W43xWR8GeX4pbaR4guw.png

I want to extract each date info from this ( Hour, Year, Month, Week, Day, Weekday) to make different filters from this. I used the following code:

Hour(Fecha) as Hora,

Year(Fecha) as Año.

month(Fecha) as Mes,

week(Fecha) as Semana,

day(Fecha) as Dia,

WeekDay(Fecha) as Dia_Semana,

But i got two problems. The first one, is that the Year is showing another info:

AsHpaKD1RjqYRTPv8wyvKQ.png

And when i try to get the Hour from the Timestamps, qlikview doesnt respond and i have to closed it.

Anyone know another form to get the Year and Hour info from this? I searched for differents solutions but anyone helped.

Thank you!

5 Replies
sunny_talwar

Images aren't showing up... can you repost

Gysbert_Wassenaar

That looks like a unix timestamp. Try =Date(MakeDate(1970, 1, 1) + (Fecha / 86400))


talk is cheap, supply exceeds demand
MarcoWedel

Hi,

guessing from your sample values, your Fecha timestamps might have millisecond resolution.

So one way of converting them into proper QV timestamps might be:

QlikCommunity_Thread_267068_Pic1.JPG

tabFecha:

LOAD *,

    Hour(Fecha)    as Hora,

    Year(Fecha)    as Año,

    Month(Fecha)  as Mes,

    Week(Fecha)    as Semana,

    Day(Fecha)    as Dia,

    WeekDay(Fecha) as Dia_Semana;

LOAD Fecha as FechaNum,

    Timestamp(25569+Fecha/864E5) as Fecha

INLINE [

    Fecha

    1,49847E+12

    1,49848E+12

    1,49849E+12

    1,49850E+12

    1,49851E+12

    1,49852E+12

    1,49853E+12

    1,49854E+12

    1,49855E+12

    1,49856E+12

    1,49857E+12

] (delimiter is spaces);

hope this helps

regards

Marco

Not applicable
Author

Thank you so much! But now i have the problem that i cant match tabFecha table with the tables with all the data to filter it.

I tried match the number of each date but doesnt match anyway:

1 = Date field in datatable

2=  Date field in tabFecha

RGVxUqbbTPKFV-enwQwEMA.png

If i try to convert it to number doesn match because each number show me it as:

8svp262IRXaxAccjQB_TfA.png

My original Data table have this kind of data:

eQWn-Bx7Ta24415KHb30Ew.png

Im looking for another way to do it but i cant find something yet.

Thank you!

MarcoWedel

Are you matching tables using the Fecha field?

You might have some small differences in timestamp values you want to match due to rounding issues.

See also:

Correct Time Arithmetic | Qlikview Cookbook  by Rob Wunderlich


Rounding Errors


convert seconds to hh:mm:ss in script


Reference DateTime for calculating occupancy


hope this helps

regards

Marco