Grouping rows

[ngosz4074]

Hello,

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

Table

ID

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

[sunny_talwar]

What was the exact script used?

[ngosz4074]

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

;

[sunny_talwar]

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

[vinieme12]

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

[ngosz4074]

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?

[ngosz4074]

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

[sunny_talwar]

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;

[vinieme12]

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

[vinieme12]

You are grouping by 3 different combinations

ID | Name | 4 Week Flag | 8 Week Flag | 12 Week Flag | Ordered Date

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

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

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