Count of Value from the Given Date time + 1Hour

[ishanbansal1204]

Hi Experts,

I have a Dataset like:

Date - Time	Dimension1	Dimension2	Count
10-1-2020 00:18:15	A	B	2
10-1-2020 01:18:00	A	B	3
10-1-2020 01:45:00	A	B	2
10-1-2020 01:48:00	A	B	1
10-1-2020 03:02:00	B	A	5

The DateTime column that we see in the Dataset has 2 dimensions and the final display is the count of values with respect to the Date Time + 1Hour. 

Record 1 : Given Date : 01/10/2020 12:18:15 AM  Date + 1Hour : 01/10/2020 01:18:15 AM 

So between these 2 dates I need to find out the count of matching records. For this date range there are 2 records which are within 1 hour so the count is 2. 

Requesting your help on this.

Thanks.

[Taoufiq_Zarra]

@ishanbansal1204  One solution in the Script Level :

Data:
LOAD timestamp(Timestamp#([Date - Time],'M-D-YYYY hh:mm:ss')) as [Date - Time], Dimension1, Dimension2,timestamp(Timestamp#([Date - Time],'M-D-YYYY hh:mm:ss') + 1/24) as EndDateTime  INLINE [
    Date - Time, Dimension1, Dimension2
    10-1-2020 00:18:15, A, B
    10-1-2020 01:18:00, A, B
    10-1-2020 01:45:00, A, B
    10-1-2020 01:48:00, A, B
    10-1-2020 03:02:00, B, A
];

Final:

LOAD 1 as Junk

autogenerate 1;


LET NumRows=NoOfRows('Data');

FOR i=0 to $(NumRows)-1


  LET vDateTimeB=Peek('Date - Time',$(i),'Data');
  LET vDateTimeE=Peek('EndDateTime',$(i),'Data');
 
Join(Final)
 
	load *,$(i) as Idtmp,'$(vDateTimeB)' as DateTimeTmp ;
	load * resident Data where [Date - Time]>='$(vDateTimeB)' and [Date - Time]< '$(vDateTimeE)';
	
	
NEXT;

drop table Data;


output:
noconcatenate

load distinct  DateTimeTmp as [Date - Time],Dimension1,Dimension2 resident Final;

left join load DateTimeTmp as [Date - Time],count([Date - Time]) as Count resident Final group by DateTimeTmp ;

drop table Final;

Input:

output:

[ishanbansal1204]

Thank you Taoufiq. Is there a way I can do that In measure directly in the visualisation?

[Taoufiq_Zarra]

@ishanbansal1204  maybe, but I don't have an idea yet 😐