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]

try this

Test:
LOAD *, Date(round(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

];

[Enno86]

Try using floor function, as it will extract integer value from your timestamp.

The integer value represents a date, or at least will push the time to '0' which would be midnight. 

[SatyaPaleti]

Hi,

Thanks for your reply. After Implementing formula you provided we are getting date values but count is still 10. We need to show count should be 2 only

Thanks,

Satya

[PrashantSangle]

you have to use distinct in your expression. 

[qv_testing]

LOAD *, Date(round(Date#([Time Stamp], 'YYYY-MM-DD h:mm:ss')),'YYYY-MM-DD') as NewDate;
you have to take COUNT(NewDate)

[SatyaPaleti]

Hi Prashanth,

Thank you so much for your response. 

Here Distinct is not working We need count of Rows in the table. I have implemented below code.

Test:
LOAD *, Date(round(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;

Still the count of row numbers is 10 even we using Distinct. Count should be 2

Thanks,

Satya

[SatyaPaleti]

Yes, Agree. It will show expected values for count(NewDate). But we need to calculate rowno()

[qv_testing]

then you can drop field Time_Stamp. 

Test:
LOAD *, Date(round(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

];

Drop field Time_Stamp from Test;

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

[SatyaPaleti]

It's not working