load all fields for minimum value

[Qlik_ULG]

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	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

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?

[andrey_krylov]

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;

[Frank_Hartmann]

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;