topic Re: Data between two date and time in QlikView

Data between two date and time

sudhir0538 — Sat, 16 Nov 2024 21:13:16 GMT

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.

Re: Data between two date and time

Shubham_Deshmukh — Tue, 19 Mar 2019 06:48:36 GMT

Which count do you want? GPQTY?

Re: Data between two date and time

Shubham_Deshmukh — Tue, 19 Mar 2019 06:57:50 GMT

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

Re: Data between two date and time

Vegar — Tue, 19 Mar 2019 08:32:23 GMT

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

Re: Data between two date and time

HirisH_V7 — Tue, 19 Mar 2019 08:25:20 GMT

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

Re: Data between two date and time

sudhir0538 — Tue, 19 Mar 2019 09:56:35 GMT

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 !!

Re: Data between two date and time

sudhir0538 — Tue, 19 Mar 2019 09:57:53 GMT

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 !!

Re: Data between two date and time

HirisH_V7 — Tue, 19 Mar 2019 11:36:52 GMT

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: