Date wise sequence count

[lakshman1031]

Hi Team,

I have a date field like MM/DD/YYYY HH:MM

Date

04/2/2025 5:10

04/2/2025 5:11

04/2/2025 5:12

04/2/2025 5:15

05/2/2025 7:11

05/2/2025 7:26

05/2/2025 11:11

05/2/2025 11:50

OutPut:

------------------------

Date                    Sequence Count

04/2/2025 5:10           1

04/2/2025 5:11           2

04/2/2025 5:12         3

04/2/2025 5:15         4

05/2/2025 7:11         1

05/2/2025 7:26         2

05/2/2025 11:11      3

05/2/2025 11:50      4

I have used number of formulas like Date, floor, using rank also but I didn't get any soulutions.

Could you please help me.

Advance Thanks.

Ram

[adilio_silva]

You can try something like this:

tbl:
LOAD *,
Window(WRank(0,1), New_Date, 'Asc', New_Time) as RankDate
;
LOAD *,
DATE#(Date_Date, 'MM/DD/YYYY') as New_Date,
TIME#(Time_Time, 'h:mm') as New_Time
;
LOAD *,
Timestamp#(Date_Field, 'MM/DD/YYYY h:mm') as TimeStamp_Field,
SubField(Date_Field, ' ',1) as Date_Date,
SubField(Date_Field, ' ',2) as Time_Time
;
LOAD * INLINE [
Date_Field
04/02/2025 5:10
04/02/2025 5:11
04/02/2025 5:12
04/02/2025 5:15
05/02/2025 7:11
05/02/2025 7:26
05/02/2025 11:11
05/02/2025 11:50
];

fnl:
LOAD 'final' as tbl,
* 
RESIDENT tbl
ORDER BY New_Date, New_Time
;

DROP TABLE tbl;

EXIT SCRIPT;

[Chanty4u]

Try this 

Data:

LOAD 

    Timestamp(Date#(Date,'MM/DD/YYYY hh:mm')) as DateTime,

    Date(Floor(Date#(Date,'MM/DD/YYYY hh:mm'))) as Day

INLINE [

Date

04/2/2025 5:10

04/2/2025 5:11

04/2/2025 5:12

04/2/2025 5:15

05/2/2025 7:11

05/2/2025 7:26

05/2/2025 11:11

05/2/2025 11:50

]

;

NoConcatenate

Final:

LOAD

    DateTime,

    Day,

    AutoNumber(RowNo(), Day) as SequenceCount

Resident Data

Order by Day, DateTime;

Front end 

Dimension: DateTime

Measure: RangeSum(Above(Count(DateTime),0,RowNo()))

[Sivapriya_d · Accepted solution]

Hi ,
Please try 

Temp:
Load Date_Field,Floor(Date_Field) as Date_Only;
LOAD Timestamp#(Date_Field,'DD/MM/YYYY h:mm') as Date_Field INLINE [
Date_Field
04/02/2025 5:10
04/02/2025 5:11
04/02/2025 5:12
04/02/2025 5:15
05/02/2025 7:11
05/02/2025 7:26
05/02/2025 11:11
05/02/2025 11:50
];

Temp2:
NoConcatenate
Load
Date_Field,
Date_Only
Resident Temp
Order by Date_Field asc;

Drop Table Temp;

Final_Table:

Load Date_Field,
IF(Previous(Date_Only)<>Date_Only,1,1+Peek(RowNum)) as RowNum
Resident Temp2;

Drop Table Temp2;