Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
Qlik_ULG
New Contributor III

load all fields for minimum value

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 IDDueDateCompletedDateProjectNumberCreated DateTaskCreatedBy
12018-11-07 11:30:002018-11-10 11:30:0012018-11-10 10:30:00345
22018-11-07 12:30:002018-11-10 12:30:0012018-11-10 11:30:00456
32018-11-07 12:00:002018-11-10 12:00:0012018-11-10 11:00:00789
72018-11-10 12:30:002018-11-10 12:30:0022018-11-10 11:30:00
345
82018-11-10 11:30:002018-11-10 11:30:002
2018-11-10 10:30:00
753
92018-11-10 12:00:002018-11-10 12:00:0022018-11-10 11:00:00456

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?

2 Replies
andrey_krylov
Valued Contributor

Re: load all fields for minimum value

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
Honored Contributor II

Re: load all fields for minimum value

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;