Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to group records

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

1 Solution

Accepted Solutions
sunny_talwar

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;

View solution in original post

6 Replies
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 |);


Capture.PNG

sunny_talwar

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)

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Not applicable
Author

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?