topic Re: Grouping rows in QlikView

Grouping rows

ngosz4074 — Tue, 26 Sep 2017 18:45:50 GMT

Hello,

I'm trying to group a series of rows and tried using which I thought should be fairly simple in the attachment.

Table

max(4 Week Pct Value),

max(8 Week Pct Value),

max(12 Week Pct Value)

Group by ID;

But in the grouping, I get 3 rows back instead of 1. I am looking into the underlying data for the long term solution, but in the short-term is there something I can do in the script or an expression to group the data together into one row?

I also tried using the peek() function looking at the Previous ID.

Thanks in advance

Re: Grouping rows

sunny_talwar — Tue, 26 Sep 2017 18:55:32 GMT

What was the exact script used?

Re: Grouping rows

ngosz4074 — Tue, 26 Sep 2017 19:02:00 GMT

Here's the script:

Original_Table

Load

ID,

Name,

Department_ID

[4 Week Target Flag],

[8 Week Target Flag],

[12 Week Target Flag],

[4 Week Target Value],

[8 Week Target Value],

[12 Week Target Value]

;

MAX_VALUE:

load ID,

max([ 4 Week Target Value]) as [4 Week Target Value test],

max([8 Week Target Value]) as [8 Week Target Value test],

max([12 Week Target Value]) as [12 Week Target Value test]

resident Original_Table

group by IID

;

Re: Grouping rows

sunny_talwar — Tue, 26 Sep 2017 19:03:45 GMT

Do you drop your original_table after MAX_VALUE before you check the result on the front end?

Re: Grouping rows

vinieme12 — Tue, 26 Sep 2017 19:04:17 GMT

What does the input data look like?

You getting separate rows only if there are other columns that have different values and when your group by all the other fields it's like creating a pivot table in excel!! they will come up in separate lines

Re: Grouping rows

ngosz4074 — Tue, 26 Sep 2017 19:12:26 GMT

I don't - there are 7-8 other fields in the original table. Would I have to apply a mass group by the dimension fields?

Re: Grouping rows

ngosz4074 — Tue, 26 Sep 2017 19:13:04 GMT

The Excel attachment is what I'm looking at currently.

Re: Grouping rows

sunny_talwar — Tue, 26 Sep 2017 19:16:47 GMT

May be try this

Original_Table:

Load

ID,

Name,

Department_ID

[4 Week Target Flag],

[8 Week Target Flag],

[12 Week Target Flag],

[4 Week Target Value],

[8 Week Target Value],

[12 Week Target Value]

FROM ...;

MAX_VALUE:

load ID,

Only(Name) as Name,

Only(Department_ID) as Department_ID,

Max([ 4 Week Target Value]) as [4 Week Target Value],

FirstSortedValue([4 Week Target Flag], -[4 Week Target Value]) as [4 Week Target Flag],

Max([8 Week Target Value]) as [8 Week Target Value],

FirstSortedValue([8 Week Target Flag], -[8 Week Target Value]) as [8 Week Target Flag],

Max([12 Week Target Value]) as [12 Week Target Value],

FirstSortedValue([12 Week Target Flag], -[12 Week Target Value]) as [12 Week Target Flag],

Resident Original_Table

Group By ID;

DROP Table Original_Table;

Re: Grouping rows

vinieme12 — Tue, 26 Sep 2017 19:18:01 GMT

Just do a pivot in excel itself and you will understand why you are getting 3 separate rows

ID	Name	4 Week Flag	8 Week Flag	12 Week Flag	Ordered Date	Max of Pct Value	Max of Pct Value2	Max of Pct Value3
11	ID Description	Nothing	N	Nothing	14-02-2017	0	64.4
11	ID Description	Nothing	Nothing	Nothing	14-02-2017	0	0
11	ID Description	Nothing	Nothing	Y	14-02-2017	0	0	96.6
Grand Total	0	64.4	96.6

Re: Grouping rows

vinieme12 — Tue, 26 Sep 2017 19:20:37 GMT

You are grouping by 3 different combinations

11 | ID Description | Nothing | N | Nothing| 42780

11 | ID Description | Nothing | Nothing | Y | 42780

Re: Grouping rows

ngosz4074 — Tue, 26 Sep 2017 19:23:28 GMT

I have seen it via Pivot table.

Re: Grouping rows

qliksus — Fri, 29 Sep 2017 09:19:29 GMT

The below script seems to give your required result not sure what issue you are facing

LOAD Distinct ID,

Name,

MaxString([4 Week Flag]) as [4 Week Flag],

MaxString([8 Week Flag]) as [8 Week Flag],

MaxString([12 Week Flag]) as [12 Week Flag]

FROM

[\Example.xlsx]

(ooxml, embedded labels, header is 1 lines, table is Sheet1)

Group by ID,Name;