Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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

Re: How to group records

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;

6 Replies

Re: How to group records

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

Re: How to group records

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;

Re: How to group records

Create Pivot table

Dimension:

Order,

State

Expression:

max(Date)

Drag and drop state field horizontally over expression

Re: How to group records

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

Life is so rich, and we need to respect to the life !!!
Not applicable

Re: How to group records

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.

Re: How to group records

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?

Community Browser