Load only first record per ID

[pauldamen]

All,

I have a dataset which looks like this:

ID          Status               Date

1               Declined          1/1

1               Declined          3/1

1               Approved          5/1

2               Approved       1/1

3               Declined          2/1

3               Approved          3/1

What I need is only to load the first record of every ID, so I get this load:

ID          Status               Date

1               Declined          1/1

2               Approved      1/1

3               Declined          2/1

How can I achieve this?

Regards,

Paul

[pipuindia99]

Hi,

Try this

load

firstsortedvalue(ID) as ID,

status,

Date

from table name group by status, date;

[Anonymous]

LOAD

firstsortedvalue(Date,Date) as Date,

firstsortedvalue(Status,Date) as Status,

ID

resident...

group by ID;

-------------------------

But you have to be carefull, when the first sorted value isn't unique within each dimension.

[pipuindia99]

Hi,

sorry, Try the below one

load

firstsortedvalue(ID , ID) as ID,

status,

Date

from table name group by status, date;

[pauldamen]

I still get multiple lines for each ID. Please find my workbook attached

[pipuindia99]

Have you attached your workbook? because i could not find it

[Anonymous]

LOAD * INLINE [
ID, Status, Date
1, Declined , 1/1
1, Declined , 3/1
1, Approved, 5/1
2, Approved, 1/1
3, Declined , 2/1
3, Approved, 3/1
]
Where not Exists(ID); 

(works only, if your table is sorted the correct way, but you said, you want the first record)

[sunny_talwar]

You can try this:

Table:

LOAD * INLINE [

    ID, Status, Date

    1, Declined, 1/1

    1, Declined, 3/1

    1, Approved, 5/1

    2, Approved, 1/1

    3, Declined, 2/1

    3, Approved, 3/1

];

Right Join (Table)

LOAD ID,

  Min(Date) as Date

Resident Table

Group By ID;

[pauldamen]

Sorry closed the browser to fast

[sunny_talwar]

Try this:

FirstValue:

LOAD Winkelwagen as ID,

  FirstSortedValue(Status, -[Aanmaak/herzien datum]) as Status,

     Date(Min([Aanmaak/herzien datum])) as Date

FROM

[Workflow rapportage SRM v0.2.xlsx]

(ooxml, embedded labels, table is [Mogelijkheid 1])

Group By Winkelwagen;