Qlik Community

Ask a Question

New to QlikView

If you’re new to QlikView, start with this Discussion Board and get up-to-speed quickly.

Announcements
Do More with Qlik - for Beginners and Beyond, Topic: Qlik Replicate on January 21, 2PM EST. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor III
Contributor III

Pivot table - show first dimension value based on row level condition

Helo,

I have data like this

LOAD * INLINE [

    F1, F2, F3, F4, F5, F6

    a1, b1, c1, d1, e1, -2

    a1, b1, c1, d1, e2, -2

    a1, b1, c1, d1, e3, -3

    a2, b2, c2, d2, e2, -24

    a3, b3, c3, d3, e1, -10

];

On the sheet, I used a listbox to select F5s that I want to see. I also used an inputbox to specify the sum(F6) values that I want to see.

If i select F5 = e1, e2 and give -2 in the inputbox, the pivot table display below rows.

   a1, b1, c1, d1, e1, -2

   a1, b1, c1, d1, e2, -2

Question - Is it possible to display just one of that row? That is, if F1, F2, F3, F4 are same and if sum(F6) is also same then just display one of the F5, maybe the first one.

Attached the qvw.

12 Replies

Nice

Contributor III
Contributor III

I hate to continue this thread after marking the correct answer. Since we already set the context I am continuing but pls let me know if you need a new thread.

The requirement changed. The value i entered before in the input box are in an island table now.

LOAD * INLINE [

SF7, SF8

S1, -5

S2, -24

];

The pivot table now has SF7 field and sum of F6 is shown if it matches SF8.

Pls look at the attached file.

The "Table 2" pivot table shows the records correctly with more than one F5.

To reduce it to min F5, in "Table 3", I applied your minstring logic like this

IF(Sum(Total <F1, F5> F6)=SF8,

If(MinString(TOTAL <F1, F2, F3, F4, SF7> Aggr(IF(Sum(Total <F1, F5> F6)=SF8, F5), F1, F2, F3, F4, F5)) = F5,

Sum(F6))

, 0

)

I was thinking the aggr will consider SF7/SF8 of that row only but looks like it includes both values.

How do we make the aggr use the SF7/SF8 corresponding to the row?

May be this


IF(Sum(Total <F1, F5> F6)=SF8,

If(MinString(TOTAL <F1, F2, F3, F4, SF7> Aggr(IF(Sum(Total <F1, F5> F6)=SF8, F5), F1, F2, F3, F4, F5, SF7)) = F5,

Sum(F6))

, 0

)


Capture.PNG