Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a table that looks similar to this:
User ID | Date | |
---|---|---|
1 | 01/10/16 | |
1 | 02/10/16 | |
1 | 04/10/16 | |
1 | 07/10/16 | |
2 | 01/10/16 | |
2 | 03/10/16 | |
2 | 04/10/16 | |
3 | 01/10/16 |
I would like to select both the MIN/MAX date for each user ID at load. Does anyone know how to do this? (within the script)
Try this.
LOAD [User ID], Max(Date), Min(Date)
GROUP BY [User ID]
A demo script of the statement in action is below
Demo:
DatesTable:
LOAD [User ID], Max(Date), Min(Date)
GROUP BY [User ID]
;
LOAD * Inline
[
'User ID' , Date
1 , 01/10/16
1 , 02/10/16
1 , 04/10/16
1 , 07/10/16
2 , 01/10/16
2 , 03/10/16
2 , 04/10/16
3 , 01/10/16
]
;
EXIT Script
;
May be this
Table:
LOAD [User ID],
Date,
OtherFields
FROM ....
TempTable:
NoConcatenate
LOAD [User ID],
Min(Date) as Date
Resident Table
Group By [User ID];
Concatenate (TempTable)
LOAD [User ID],
Max(Date) as Date
Resident Table
Group By [User ID];
Right Join (Table)
LOAD *
Resident TempTable;
DROP Table TempTable;