topic Re: GROUP LOAD 1st Record in QlikView

GROUP LOAD 1st Record

richard_chilvers — Mon, 22 Feb 2016 11:21:25 GMT

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

Re: GROUP LOAD 1st Record

sunny_talwar — Mon, 22 Feb 2016 11:26:28 GMT

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;

Re: GROUP LOAD 1st Record

richard_chilvers — Mon, 22 Feb 2016 11:52:41 GMT

Thanks !