Hi Team,
I need to take 5 distinct date rows based on for each category and date column.
If 5 are not available below logic follows
4 distinct dates available --we need to pick 4 distinct date row+1 random row
3 distinct dates available --we need to pick 3 distinct date row+2 random rows
2 distinct dates available --we need to pick 2 distinct date row+3 random rows
1 distinct dates available --we need to pick 5 random rows.
Sample data:
| Category | Brand | Model | Modification ate |
|---|---|---|---|
| abc1 | exe0001 | rgg345 | 6/9/2024 |
| abc2 | exe0002 | rgg346 | 6/9/2024 |
| abc3 | exe0003 | rgg347 | 6/9/2024 |
| abc4 | exe0004 | rgg348 | 7/9/2024 |
| abc5 | exe0005 | rgg349 | 7/9/2024 |
| abc6 | exe0006 | rgg350 | 7/9/2024 |
| abc7 | exe0007 | rgg351 | 7/9/2024 |
| abc8 | exe0008 | rgg352 | 7/9/2024 |
| abc9 | exe0009 | rgg353 | 7/9/2024 |
| abc1 | exe0001 | rgg345 | 6/9/2024 |
| abc1 | exe0001 | rgg345 | 6/9/2024 |
| abc1 | exe0001 | rgg345 | 6/9/2024 |
| abc1 | exe0001 | rgg345 | 6/9/2024 |
| abc1 | exe0001 | rgg345 | 6/9/2024 |
| abc1 | exe0001 | rgg345 | 6/10/2024 |
| abc1 | exe0001 | rgg345 | 6/11/2024 |
| abc1 | exe0001 | rgg345 | 6/12/2024 |
output data:(above sample we have 4 distinct dates. we need to pick 4 distinct date rows+1 random row with any date)
| Category | Brand | Model | Modification ate |
|---|---|---|---|
| abc1 | exe0001 | rgg345 | 6/9/2024 |
| abc1 | exe0001 | rgg345 | 6/9/2024 |
| abc1 | exe0001 | rgg345 | 6/10/2024 |
| abc1 | exe0001 | rgg345 | 6/11/2024 |
| abc1 | exe0001 | rgg345 | 6/12/2024 |
Could you please help me on this. TIA