Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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 |);
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;
Create Pivot table
Dimension:
Order,
State
Expression:
max(Date)
Drag and drop state field horizontally over expression
Kushal, If we take Date Directly output will come. Can i know the reason why are taking Max(Date)
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.
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?