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

[pauldamen]

Not quite. Now I only get approved ID's. I think it needs to sort different, now it takes the first status

Regards, Paul

[Anonymous]

Do you need the first value of every ID or the firstsortedvalue of every ID?

If you need the firstsortedvalue you have the specify the desired sort weight...

[pauldamen]

I need the first ID per date.

[Anonymous]

so if I take Sunny's solution as base, you could try that:

FirstValue:

LOAD Winkelwagen as ID,

FirstSortedValue(distinct 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;

[sunny_talwar]

My bad, I added a negative sign... 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;

[yoganantha321]

Hi,

As sunny mentioned it is working properly

[Anonymous]

ya Its working  correctly thanks its helpful to me

[MarcoWedel · Accepted solution]

Hi,

another solution might be:

tabTemp:
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
];

NoConcatenate

table1:
LOAD * Resident tabTemp
Where ID<>Previous(ID)
Order By ID, Date;

DROP Table tabTemp;

hope this helps

regards

Marco