Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I'm stuck with trying to load only one record per ID from my table. I want to load the whole row, but only for the minimum TaskID, per ProjectNumber.
Task ID | DueDate | CompletedDate | ProjectNumber | Created Date | TaskCreatedBy | ||
---|---|---|---|---|---|---|---|
1 | 2018-11-07 11:30:00 | 2018-11-10 11:30:00 | 1 | 2018-11-10 10:30:00 | 345 | ||
2 | 2018-11-07 12:30:00 | 2018-11-10 12:30:00 | 1 | 2018-11-10 11:30:00 | 456 | ||
3 | 2018-11-07 12:00:00 | 2018-11-10 12:00:00 | 1 | 2018-11-10 11:00:00 | 789 | ||
7 | 2018-11-10 12:30:00 | 2018-11-10 12:30:00 | 2 | 2018-11-10 11:30:00 |
| ||
8 | 2018-11-10 11:30:00 | 2018-11-10 11:30:00 | 2 |
| 753 | ||
9 | 2018-11-10 12:00:00 | 2018-11-10 12:00:00 | 2 | 2018-11-10 11:00:00 | 456 |
As you can see, all rows are unique with different dates but I only want to load the first task for every ProjectNumber.
I am currently creating a temp table, and then loading the minimum TaskID associated with each ProjectNumber, joining it back to the temp table and performing a resident load to get the subsequent tables. but this is not working for me.
Load
TASK_temp
TaskID
,[DueDate]
,[CompletedDate]
,[ProjectNumber]
,[Created Date]
,[TaskCreatedBy]
From
TASK_TABLE
Task_MIN
inner join(TASK_temp)
LOAD
min(TaskID) as Task_ID,
[ProjectNumber]
Resident TASK_temp
group by ProjectNumber;
TASK
LOAD
TaskID
,[DueDate]
,[CompletedDate]
,[ProjectNumber]
,[Created Date]
,[TaskCreatedBy]
Resident TASK_temp;
Drop table TASK_temp;
Am I doing something wrong here?
Does anyone know how to load all columns, but only for the minimum TaskID?
Hi Shane. Maybe a little bit syntax
TASK: NoConcatenate
Load
TaskID
,[DueDate]
,[CompletedDate]
,[ProjectNumber]
,[Created Date]
,[TaskCreatedBy]
From TASK_TABLE;
Inner join LOAD
min(TaskID) as Task_ID,
[ProjectNumber]
Resident TASK
group by ProjectNumber;
Or maybe like that:
TEMP:
LOAD * INLINE [
TaskID, DueDate, CompletedDate, ProjectNumber, Created Date, TaskCreatedBy
1, 2018-11-07 11:30:00, 2018-11-10 11:30:00, 1, 2018-11-10 10:30:00, 345
2, 2018-11-07 12:30:00, 2018-11-10 12:30:00, 1, 2018-11-10 11:30:00, 456
3, 2018-11-07 12:00:00, 2018-11-10 12:00:00, 1, 2018-11-10 11:00:00, 789
7, 2018-11-10 12:30:00, 2018-11-10 12:30:00, 2, 2018-11-10 11:30:00, 345
8, 2018-11-10 11:30:00, 2018-11-10 11:30:00, 2, 2018-11-10 10:30:00, 753
9, 2018-11-10 12:00:00, 2018-11-10 12:00:00, 2, 2018-11-10 11:00:00, 456
10, 2018-11-10 12:00:00, 2018-11-10 12:00:00, 3, 2018-11-10 11:00:00, 344
];
noconcatenate
Load * Resident TEMP where if(ProjectNumber=Previous(ProjectNumber),TaskID)<>TaskID; DROP Table TEMP;