How to group records

[#1]

Hi all

I have a table like this:

Order | State                 | Date

A        | To be Receive  | 01/08/2016

A        | Received          | 04/08/2016

A        | Closed              | 05/08/2016

B        | To be Receive  | 07/07/2016

B        | Received          | 08/07/2016

B        | Closed              | 09/07/2016

I would to be able to obtain something like this:

Order | To be Received | Received     | Closed

A        | 01/08/2016        | 04/08/2016 | 05/08/2016

A        | 07/07/2016        | 08/07/2016 | 09/07/2016

Any ideas?

Thank in advance

Michele

[sunny_talwar]

Option 1: Using Generic load: (The Generic Load)

Table:

Generic

LOAD * Inline [

Order | State                | Date

A        | To be Receive  | 01/08/2016

A        | Received          | 04/08/2016

A        | Closed              | 05/08/2016

B        | To be Receive  | 07/07/2016

B        | Received          | 08/07/2016

B        | Closed              | 09/07/2016

] (delimiter is |);

[sunny_talwar · Accepted solution]

Option 2: Using Joins

Table:

LOAD * Inline [

Order | State                | Date

A        | To be Receive  | 01/08/2016

A        | Received          | 04/08/2016

A        | Closed              | 05/08/2016

B        | To be Receive  | 07/07/2016

B        | Received          | 08/07/2016

B        | Closed              | 09/07/2016

] (delimiter is |);

FinalTable:

LOAD Order,

  Date as [To be Receive]

Resident Table

Where State = 'To be Receive';

Join (FinalTable)

LOAD Order,

  Date as [Received]

Resident Table

Where State = 'Received';

Join (FinalTable)

LOAD Order,

  Date as [Closed]

Resident Table

Where State = 'Closed';

Drop Table Table;

[Kushal_Chawda]

Create Pivot table

Dimension:

Order,

State

Expression:

max(Date)

Drag and drop state field horizontally over expression

[Anil_Babu_Samineni]

Kushal, If we take Date Directly output will come. Can i know the reason why are taking Max(Date)

[#6]

Yes... with pivot table is simple to obtain what i need... but with pivot table i can't sort by expression and my users must to be able to do that.

[sunny_talwar]

What is the sorting requirement? I don't understand why won't you be able to sort it? Can you provide the expected output with relation to the sorting?