Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
sudhir0538
Contributor

Data between two date and time

Hello Community,

I've data of vehicle movement which includes gate in & gate out date and time in different fields and requirement is to generate a report how many for vehicle movement how many in and how many out day wise.In my company the day is calculated as from previous day 6AM to next day 6AM.

Can any one guide how to do it? Sample data attached.

1 Solution

Accepted Solutions
HirisH_V7
Honored Contributor

Re: Data between two date and time

Hi Check this revised code and output as desired.

 

Data:
LOAD 
Timestamp(GEDATE + GETIME,'DD-MM-YYYY hh:mm:ss') as INTIME,
If(Isnull(Timestamp(GATEOUTDATE + GATEOUTTIME,'DD-MM-YYYY hh:mm:ss')),TimeStamp(Now(),'DD-MM-YYYY hh:mm:ss'),
Timestamp(GATEOUTDATE + GATEOUTTIME,'DD-MM-YYYY hh:mm:ss'))as OUTTIME,
GEDATE, GETIME, GATEENTRYNO, GATENO, GPQTY, GATEOUTDATE, GATEOUTTIME
FROM [Gate In Out.xlsx] (ooxml, embedded labels, table is Sheet1);

Min_Max:
Load Min(INTIME) as vINMin,
     Max(INTIME) as vINMax,
     Min(OUTTIME) as vOUTMin,
     Max(OUTTIME) as vOUTMax
     Resident Data;
     

Let vINMax = Timestamp(Date(Floor(Peek('vINMax')))+ '06:00:00','DD-MM-YYYY hh:mm:ss');

Let vINMin = Timestamp(Date(Floor(Peek('vINMin')))+ '06:00:00','DD-MM-YYYY hh:mm:ss');

Let vOUTMax = Timestamp(Date(Floor(Peek('vOUTMax')))+ '06:00:00','DD-MM-YYYY hh:mm:ss');

Let vOUTMin = Timestamp(Date(Floor(Peek('vOUTMin')))+ '06:00:00','DD-MM-YYYY hh:mm:ss');

Drop table Min_Max;

Out put in UI:Capture.JPG

 

 

 

 

 

 

 

HirisH
“Aspire to Inspire before we Expire!”
7 Replies
Shubham_Deshmukh
Valued Contributor

Re: Data between two date and time

Which count do you want? GPQTY?
Shubham_Deshmukh
Valued Contributor

Re: Data between two date and time

Try this :

=INTERVAL(DATE(GATEOUTDATE+ GATEOUTTIME)-DATE(GEDATE + [GETIME]))

OR if you want it to show with minutes, you can add parameter like : 

=INTERVAL(DATE(GATEOUTDATE+ GATEOUTTIME)-DATE(GEDATE + [GETIME]),'mm')

- Shubham

 

Partner
Partner

Re: Data between two date and time

To get the date right you will need to create an master calendar, but your will need to substract six hours from the timestamp when generating your transaction table date key. 

 

Example below: 

Transactions:
LOAD 
	floor(Timestamp - interval#(6,'h')) as %date , //skew 6 hours
	Timestamp, 
	Animal
INLINE [
Timestamp,Animal
2019-03-06 01:18:29,Common brushtail possum
2019-03-06 03:55:31,Indian leopard
2019-03-06 05:44:54,Netted rock dragon
2019-03-06 06:22:29,Grey mouse lemur
2019-03-06 07:25:58,Javanese cormorant
2019-03-05 10:59:51,"Sandgrouse, yellow-throated"
2019-03-06 05:31:29,"Reedbuck, bohor"
2019-03-06 06:56:49,,Pied crow
2019-03-06 07:26:09,"Booby, blue-footed"
2019-03-05 00:07:22,Komodo dragon
2019-03-05 07:12:26,Common boubou shrike
2019-03-06 06:55:49,"Siskin, pine"
2019-03-06 23:52:16,Thomson's gazelle
];

Calendar:
LOAD 
  num(Date) as %date,
  Date,
  year(Date) as Year,
  Week(Date) as Week,
  Weekday(Date) as Weekday
INLINE [
  Date
  2019-03-06
  2019-03-05
  2019-03-04
];

???
Celebrating my 300 accepted solution in the Qlik Community
???
HirisH_V7
Honored Contributor

Re: Data between two date and time

Hi Check out this below code,

Data:
Load *,
If(Floor(INTIME)=Floor(OUTTIME) and Hour(INTIME)<6,'1 D  ' &  Interval(OUTTIME-INTIME,'hh:mm:ss'),
If(Floor(INTIME)<>Floor(OUTTIME) and Hour(INTIME)<6,Floor(OUTTIME)-Floor(INTIME-1)  &' D ' &  Interval(OUTTIME-INTIME,'hh:mm:ss'),
Floor(OUTTIME)-Floor(INTIME)  &' D ' &  Interval(OUTTIME-INTIME,'hh:mm:ss')))
as Interval_Diff;

LOAD 
Timestamp(GEDATE + GETIME,'DD-MM-YYYY hh:mm:ss') as INTIME,
If(Isnull(Timestamp(GATEOUTDATE + GATEOUTTIME,'DD-MM-YYYY hh:mm:ss')),TimeStamp(Now(),'DD-MM-YYYY hh:mm:ss'),
Timestamp(GATEOUTDATE + GATEOUTTIME,'DD-MM-YYYY hh:mm:ss'))as OUTTIME,
GEDATE, GETIME, GATEENTRYNO, GATENO, GPQTY, GATEOUTDATE, GATEOUTTIME
FROM [Gate In Out.xlsx] (ooxml, embedded labels, table is Sheet1);

From the sample data provided, created above. It will give the Interval in time as well as no of days in between. Based on your below 6 and above 6 logic.

If required as calendar means,let me know all occurrences of this. we can use daystart function for such cases.

Anyway, above doesn't work means. let me know your desired output for all the occurrences.

PFA QVW For ref.

HTH,

Hirish

 

HirisH
“Aspire to Inspire before we Expire!”
sudhir0538
Contributor

Re: Data between two date and time

Hi Subham, I want to count how many vehicles are In and How many vehicles are out. Suppose for this day 19.03.2019 we have to count frim 18.03.2019 morning 6AM to 19.03.2019 6AM.

Thank You for suggestions above !!

sudhir0538
Contributor

Re: Data between two date and time

Hi Hirish, I want to count how many vehicles are In and How many vehicles are out. Suppose for this day 19.03.2019 we have to count frim 18.03.2019 morning 6AM to 19.03.2019 6AM.

Please help to get that !! Thank you for all the suggestions !!

HirisH_V7
Honored Contributor

Re: Data between two date and time

Hi Check this revised code and output as desired.

 

Data:
LOAD 
Timestamp(GEDATE + GETIME,'DD-MM-YYYY hh:mm:ss') as INTIME,
If(Isnull(Timestamp(GATEOUTDATE + GATEOUTTIME,'DD-MM-YYYY hh:mm:ss')),TimeStamp(Now(),'DD-MM-YYYY hh:mm:ss'),
Timestamp(GATEOUTDATE + GATEOUTTIME,'DD-MM-YYYY hh:mm:ss'))as OUTTIME,
GEDATE, GETIME, GATEENTRYNO, GATENO, GPQTY, GATEOUTDATE, GATEOUTTIME
FROM [Gate In Out.xlsx] (ooxml, embedded labels, table is Sheet1);

Min_Max:
Load Min(INTIME) as vINMin,
     Max(INTIME) as vINMax,
     Min(OUTTIME) as vOUTMin,
     Max(OUTTIME) as vOUTMax
     Resident Data;
     

Let vINMax = Timestamp(Date(Floor(Peek('vINMax')))+ '06:00:00','DD-MM-YYYY hh:mm:ss');

Let vINMin = Timestamp(Date(Floor(Peek('vINMin')))+ '06:00:00','DD-MM-YYYY hh:mm:ss');

Let vOUTMax = Timestamp(Date(Floor(Peek('vOUTMax')))+ '06:00:00','DD-MM-YYYY hh:mm:ss');

Let vOUTMin = Timestamp(Date(Floor(Peek('vOUTMin')))+ '06:00:00','DD-MM-YYYY hh:mm:ss');

Drop table Min_Max;

Out put in UI:Capture.JPG

 

 

 

 

 

 

 

HirisH
“Aspire to Inspire before we Expire!”