Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

ngosz4074
New 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

Re: Grouping rows

What was the exact script used?

ngosz4074
New Contributor III

Re: Grouping rows

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

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

vinieme12
Esteemed Contributor II

Re: Grouping rows

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

Re: Grouping rows

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

Re: Grouping rows

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

Highlighted

Re: Grouping rows

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
Esteemed Contributor II

Re: Grouping rows

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
vinieme12
Esteemed Contributor II

Re: Grouping rows

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