How to Avoid Repeated values from Timestamp?

[SatyaPaleti]

Hi Experts,

I had following data.

Test:
LOAD * INLINE
[
Emp ID,Time Stamp
EMP123,2024-01-19 08:24:16
EMP123,2024-01-19 08:28:45
EMP123,2024-01-19 08:28:46
EMP123,2024-01-19 08:28:47
EMP123,2024-01-24 11:58:55
EMP123,2024-01-24 11:58:56
EMP123,2024-01-24 11:59:52
EMP123,2024-01-24 11:59:53
EMP123,2024-01-24 12:00:43
EMP123,2024-01-24 12:00:44

];

Test1:
Load *,
RowNo() as Rowno
Resident Test;
drop table Test;

In the Time Stamp Field we have two different Dates i.e., 2024-01-19 & 2024-01-24. So, when I calculate row number for this table I have to show only count 2 rows because we have two different dates. 

How to avoid repeated values of the single date.

For Example I had 

2024-01-19 08:24:16
2024-01-19 08:28:45
2024-01-19 08:28:46
2024-01-19 08:28:47

I have to consider only one date among these 4

2024-01-24 11:58:55
2024-01-24 11:58:56
2024-01-24 11:59:52
2024-01-24 11:59:53
2024-01-24 12:00:43
2024-01-24 12:00:44

I have to consider only one date among these 6.

So finally Row number of this table should be 2. Could you please help me with your suggestions

Thanks,

Satya

[qv_testing]

Use Distinct like below

Test:
LOAD Distinct [Emp ID], Date(floor(Date#([Time_Stamp], 'YYYY-MM-DD h:mm:ss')),'YYYY-MM-DD') as NewDate;
LOAD * INLINE
[
Emp ID,Time_Stamp
EMP123,2024-01-19 08:24:16
EMP123,2024-01-19 08:28:45
EMP123,2024-01-19 08:28:46
EMP123,2024-01-19 08:28:47
EMP123,2024-01-24 11:58:55
EMP123,2024-01-24 11:58:56
EMP123,2024-01-24 11:59:52
EMP123,2024-01-24 11:59:53
EMP123,2024-01-24 12:00:43
EMP123,2024-01-24 12:00:44

];

Test1:
Load *,
RowNo() as Rowno
Resident Test;
drop table Test;

[PrashantSangle]

Try below

Test:
Load Distinct [Emp ID], NewDate;
LOAD [Emp ID], Date(floor(Date#([Time_Stamp], 'YYYY-MM-DD h:mm:ss')),'YYYY-MM-DD') as NewDate;
LOAD * INLINE
[
Emp ID,Time_Stamp
EMP123,2024-01-19 08:24:16
EMP123,2024-01-19 08:28:45
EMP123,2024-01-19 08:28:46
EMP123,2024-01-19 08:28:47
EMP123,2024-01-24 11:58:55
EMP123,2024-01-24 11:58:56
EMP123,2024-01-24 11:59:52
EMP123,2024-01-24 11:59:53
EMP123,2024-01-24 12:00:43
EMP123,2024-01-24 12:00:44
];

NoConcatenate
Final:
//Load *,RowNo() as RowNo;
Load Distinct [Emp ID], NewDate,RowNo() as RowNo
Resident Test;

Drop Table Test;

[SatyaPaleti]

No Luck Prashanth

[Ahidhar]

try this

Test:
load [Time Stamp],only([Emp ID]) as [Emp ID] group by [Time Stamp];
load [Emp ID],floor(Timestamp#([Time Stamp],'YYYY-MM-DD hh:mm:ss')) as [Time Stamp];
LOAD * INLINE
[
Emp ID,Time Stamp
EMP123,2024-01-19 08:24:16
EMP123,2024-01-19 08:28:45
EMP123,2024-01-19 08:28:46
EMP123,2024-01-19 08:28:47
EMP123,2024-01-24 11:58:55
EMP123,2024-01-24 11:58:56
EMP123,2024-01-24 11:59:52
EMP123,2024-01-24 11:59:53
EMP123,2024-01-24 12:00:43
EMP123,2024-01-24 12:00:44
];

Test1:
load [Emp ID],[Time Stamp]
,RowNo() as rowno
resident Test;drop table Test;

[vinieme12]

Use floor() instead

Date(floor(Timestamp#([Time Stamp],'YYYY-MM-DD h:mm:ss'))) as DateIs

[Anil_Babu_Samineni]

@SatyaPaleti Perhaps this?

Test:
LOAD *,AutoNumber(Date(Floor("Time Stamp")), IterNo()) as RowNo INLINE
[
Emp ID,Time Stamp
EMP123,2024-01-19 08:24:16
EMP123,2024-01-19 08:28:45
EMP123,2024-01-19 08:28:46
EMP123,2024-01-19 08:28:47
EMP123,2024-01-24 11:58:55
EMP123,2024-01-24 11:58:56
EMP123,2024-01-24 11:59:52
EMP123,2024-01-24 11:59:53
EMP123,2024-01-24 12:00:43
EMP123,2024-01-24 12:00:44
];

Output looks like this, In case this is not expected, please add the output column that you need.