GROUP LOAD 1st Record

[richard_chilvers]

I have a table of data:

CustomerID	OrderRef	OrderDetail	Data1	Data2
1234	4321	4321-1	A	B
1234	4321	4321-2	C	D
1234	4321	4321-3	E	F
1234	5432	5432-1	Z	X
2345	7654	7654-1	W	Y
2345	7654	7654-2	M	N

I want to LOAD the first record (in full) based on the OrderDetail field, for each CustomerID/OrderRef group.

However there may be many Data fields (only 2 shown) so what is the best way of writing the LOAD ? I simply want to LOAD whatever values are on the first record of each group.

Thanks

[sunny_talwar · Accepted solution]

Try this:

Table:

LOAD CustomerID,

    OrderRef,

    OrderDetail,

    Data1,

    Data2

FROM

[https://community.qlik.com/thread/206446]

(html, codepage is 1252, embedded labels, table is @1)

Where Len(Trim(CustomerID)) > 0;

Right Join (Table)

LOAD CustomerID,

  FirstValue(OrderDetail) as OrderDetail

Resident Table

Group By CustomerID

Order By CustomerID, OrderDetail;

[richard_chilvers]

Thanks !