Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
];
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.
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
you have to use distinct in your expression.
LOAD *, Date(round(Date#([Time Stamp], 'YYYY-MM-DD h:mm:ss')),'YYYY-MM-DD') as NewDate;
you have to take COUNT(NewDate)
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
Yes, Agree. It will show expected values for count(NewDate). But we need to calculate rowno()
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;
It's not working