topic Re: load all fields for minimum value in QlikView

load all fields for minimum value

Qlik_ULG — Wed, 25 Nov 2020 16:16:04 GMT

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

2018-11-07 11:30:00

2018-11-10 11:30:00

2018-11-10 10:30:00

345

2018-11-07 12:30:00

2018-11-10 12:30:00

2018-11-10 11:30:00

456

2018-11-07 12:00:00

2018-11-10 12:00:00

2018-11-10 11:00:00

789

2018-11-10 12:30:00

2018-11-10 11:30:00

345

2018-11-10 11:30:00

2018-11-10 10:30:00

753

2018-11-10 12:00:00

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?

Re: load all fields for minimum value

andrey_krylov — Thu, 08 Nov 2018 11:51:13 GMT

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;

Re: load all fields for minimum value

Frank_Hartmann — Thu, 08 Nov 2018 12:38:55 GMT

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;