Skip to main content
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
ngosz4074
Contributor III
Contributor III

Grouping rows

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

11 Replies
sunny_talwar

What was the exact script used?

ngosz4074
Contributor III
Contributor III
Author

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
Champion III
Champion III

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

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
ngosz4074
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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
Champion III
Champion III

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

        

IDName4 Week Flag8 Week Flag12 Week FlagOrdered DateMax of Pct ValueMax of Pct Value2Max of Pct Value3
11ID DescriptionNothingN Nothing14-02-2017064.4
11ID DescriptionNothingNothingNothing14-02-201700
11ID DescriptionNothingNothingY14-02-20170096.6
Grand Total064.496.6
Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
vinieme12
Champion III
Champion III

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

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.