Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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:
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
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 ];
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
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 !!
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 !!
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: