Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
cancel
Showing results for 
Search instead for 
Did you mean: 
guiecokey22
Contributor III
Contributor III

R

Hello everybody!

I have a simple question (I hope ).

I woul like to have a rank of hours like

HourInOut
9-1023
11-1225
13-1428
14-1510

So, my set of data´s is very simple, I have for each ID, a TimeStamp and a chart with In or Out as:

IDDateIn/out
117/01/2017 09:00:00IN

2

17/01/2017 09:40:00IN
317/01/2017 09:41:00OUT
417/01/2017 09:45;00OUT
517/01/2017 09:50:00OUT

Could anyone tell my how to do it?

Thank you very much!!!

1 Solution

Accepted Solutions
sunny_talwar

I guess extract hour from your Date field

Hour(Date) as Hour

and then use this as a dimension

Hour

Expression

Count({<[In/Out] = {'IN'}>}ID)

Count({<[In/Out] = {'OUT'}>}ID)

View solution in original post

5 Replies
sunny_talwar

I guess extract hour from your Date field

Hour(Date) as Hour

and then use this as a dimension

Hour

Expression

Count({<[In/Out] = {'IN'}>}ID)

Count({<[In/Out] = {'OUT'}>}ID)

View solution in original post

maxgro
MVP
MVP

script

W:

LOAD

  ID,

    Alt(Timestamp#(Date, 'DD/MM/YYYY hh:mm:ss[.fff]'), Timestamp#(Date, 'DD/MM/YYYY hh:mm;ss[.fff]')) as Date,

    [In/out]

FROM

[https://community.qlik.com/thread/252420]

(html, codepage is 1252, embedded labels, table is @2);

chart (pivot)

calculated dimensions

    =Dual(

    SubField(Class(Hour(Date), 2, 'x', 1), '<=', 1) & '-' & (SubField(Class(Hour(Date), 2, 'x', 1), '< ', 2)-1),

    Class(Hour(Date), 2, 'x', 1)

    )

dimension

In/Out

expression

count(ID)

1.png

MarcoWedel

Hi,

another solution could be:

QlikCommunity_Thread_252420_Pic1.JPG

QlikCommunity_Thread_252420_Pic3.JPG

QlikCommunity_Thread_252420_Pic2.JPG

tabSampleData:

LOAD *,

    Time#(Time(Frac(Date),'hh:mm:ss'),'hh:mm:ss') as Time;

LOAD RecNo() as ID,

    Timestamp#(Timestamp(Now()-Rand()*7,'DD/MM/YYYY hh:mm:ss'),'DD/MM/YYYY hh:mm:ss') as Date,

    Pick(Ceil(Rand()*2),'In','Out') as [In/Out]

AutoGenerate 30;

tabTime:

LOAD *,

    Second(Time) as Second,

    Minute(Time) as Minute,

    Hour(Time) as Hour2,

    Dual(Hour(Time)&'-'&(Hour(Time)+1),Hour(Time)) as Hour;

LOAD Time(Time#(RecNo()-1,'s'),'hh:mm:ss') as Time

AutoGenerate 86400;

hope this helps

regards

Marco

sasiparupudi1
Master III
Master III

May be Like this?

[Time Table]:

Load

    If(Mod(RecNo(), 2) , RecNo())as FromTime,

    If(Mod(RecNo(), 2),RecNo()+1) as ToTime

Autogenerate(24);

T4:

Load

    ID,

    Date(Floor(Timestamp#(Date,'DD/MM/YYYY hh:mm:ss'))) as RecordDate,

    Time(Frac(Timestamp#(Date,'DD/MM/YYYY hh:mm:ss')),'hh:mm')  as RecordTime,

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

    [In/out];

Load * Inline

[

ID,Date,In/out

1,17/01/2017 09:00:00,IN

2,17/01/2017 09:40:00,IN

3,17/01/2017 09:41:00,OUT

4,17/01/2017 09:45:00,OUT

5,17/01/2017 09:50:00,OUT

];

Left Join

IntervalMatch(RecordHour)

Load  FromTime,ToTime Resident [Time Table];

Drop Table [Time Table];

Fact:

NoConcatenate Load

    ID,

    RecordDate,

    RecordTime,

    RecordHour,

    [In/out],

    FromTime,

    ToTime,

    FromTime&'-'&ToTime as TimeClass

Resident T4;

Drop Table T4;

  

guiecokey22
Contributor III
Contributor III

Thank you Sunny, it worked very well !!!!