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
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;
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;
No Luck Prashanth
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;
Use floor() instead
Date(floor(Timestamp#([Time Stamp],'YYYY-MM-DD h:mm:ss'))) as DateIs
@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.