18 Replies Latest reply: Jun 21, 2013 11:37 AM by Deepak Vadithala RSS

    *** Always show one row in Pivot Table ***

    Deepak Vadithala

      Dear All,

       

      I need some help on what might seem to be a simple problem but I can't get my head around. I have mocked an example for you understanding.

       

      Problem Description:

      I have a Fact table with 11 rows and showing them in a Pivot Table.I wanted to ALWAYS show one row Irrespective of what Dimension you select. I.e. I have Dimension values from Dim 1 to Dim 10 in my Dimension table but I don't have - "Dim Always Show" in my Dimension table. So how do I always show/retain this row while users are making selections? Ideally I wanted to solve this problem in script (If possible, please!).

       

      Expected Results:

      To summarize, if I select Dim 3 for example, then I wanted to retain Dim 3 and Dim Always Show rows in my pivot table. I hope this makes sense. I'm attaching the excel file for your reference.

       

      Thanks in advance.

       

      Cheers,

      DV

        • Re: *** Always show one row in Pivot Table ***
          Stefan Wühl

          Hi DV,

           

          maybe like attached?

          • Re: *** Always show one row in Pivot Table ***
            Tresesco B

            Hi Deepak,

             

            See the attachment. The first three dimensions are included as selectable while the 'Region' is ALWAYS there.

             

             

            Thanks.

              • Re: *** Always show one row in Pivot Table ***
                Deepak Vadithala

                Thanks tresesco.

                 

                Sorry mate. I completely lost you there. Please check my latest example and probably you might have an answer.

                 

                Thanks for trying!

                 

                Cheers,

                DV

                  • Re: *** Always show one row in Pivot Table ***
                    Karthikeyan S

                    Could you clarify whether my understanding is right or not?

                     

                    As per your attachment,

                     

                    Event  user selecting "Dimension_Field2" , you want show always 10 rows in pivot table?

                     

                    If this is the requirement.. Could you duplicate on dimension table?

                     

                    Something like all values of Dimension_Field1 will link all values on Dimension_field2? (10 X10 = 100 rows)

                     

                    Regards,

                     

                    Karthik

                      • Re: *** Always show one row in Pivot Table ***
                        Deepak Vadithala

                        Hi Stefan,

                         

                        That's helpful. Many thanks again for you time. But I really don't want to use expressions because the Fact table is massive i.e. 500 million rows. This will slow down the performance and have bad user experience. Any chance of solving this within the script would be great. I'm struggling to think on how to link both the Dimension tables yet keeping the "Dim Always Show" row. I really appreciate if you can help me.

                         

                        Karthik - when I select any Dimension field it should reduce the data on the Pivot Table. however, it should somehow keep the "Dim Always Show" row. It's like I want QlikView to exlude selection only on this row and rest of the rows should work as per QV logic. Hope this makes sense. Please look at my previous attachment.

                         

                        Cheers,

                        DV

                          • Re: *** Always show one row in Pivot Table ***
                            Stefan Wühl

                            Hm, maybe just simply by connecting Dimension_Field To Select On?

                              • Re: *** Always show one row in Pivot Table ***
                                Deepak Vadithala

                                Hi Stefan,

                                 

                                This was just an example but my actual dimension tables have common key values only in Fact table. I.e. Dimension tables are not linked to each other which means we can't use this approach because we will have many-to-many relationship and once you select any dimension value then it will not just show associated values but it show everything because we are duplicating the rows "Always Show" values. Please the attachment and let me know if you have a solution for this problem.

                                 

                                Many thanks for your assistance.

                                 

                                Cheers,

                                DV

                                  • Re: *** Always show one row in Pivot Table ***
                                    Stefan Wühl

                                    Hm, if I correct the missing field name in your fact table, it doesn't look too bad to me.

                                     

                                    Could you point me to what you are missing in that sample? What do you exactely expect to see when

                                    making selections?

                                      • Re: *** Always show one row in Pivot Table ***
                                        Deepak Vadithala

                                        Hi Stefan,

                                         

                                        Please accept my apology if I wasn't clear while you're helping me. Please see my latest attachment and I have created following objects:

                                         

                                        1. Final output table (cross tabled in script and using EAV model for dynamic column selections)

                                        2. Col Selection listbox

                                        3. Dim 01, Dim 02 & Dim 03

                                         

                                        Now, if you make selections on Col Selection listbox then you are filtering rows, however I have pivoted my Attribute hence you are looking them as columns. And now I see the "Don't Filter" row no matter what I select but the problem is that I lost the power of associations. I.e when I select Dim 3 then I see all the values in Dim 1 & Dim 2 vice-versa instead of just seeing the possible value(s). How do I solve this problem without losing the "Don't Filter" row in the Final Output Table.

                                         

                                        Ps: The reason I'm using "Don't Filter" row because when we Cross Table in QlikView. We will lose the NULL values, which is perfect for very large data sets because we don't storage in rows....but in turn we are also missing those columns in the Final Output Table as they are stored as rows.

                                         

                                        I hope this makes sense. Please shout if I'm not clear.

                                         

                                        Many thanks again for your support.

                                         

                                        Cheers,

                                        DV

                                          • Re: *** Always show one row in Pivot Table ***
                                            whiteline _

                                            Hi DV.

                                             

                                            Played with your example for a while...

                                            Why don't you use the 'total' row for that purpose ?

                                             

                                            You could change the lable and then disable bold and make the background white using 'custom format cell' in advanced styling mode.

                                            Finally you should use an expression like this:

                                            =if(Dimensionality()<>0, Only(Data), 'Don''t filter')

                                             

                                            The big advantage is that the user can't select totals, while in your example he can. And this will broke everithing. The attempts to solve this can lead you to even more complex solutions with field triggers and so on.

                                             

                                            Of course, it only makes sense, if you're not supposed to use totals in your final table (as you can't define different labels for each level).

                                             

                                            PS: Another trick that usually helps is to use two or more separate overlapping objects that look like one.

                                              • Re: *** Always show one row in Pivot Table ***
                                                Deepak Vadithala

                                                Whiteline - Very clever! I like the technique of using "Total" row. I'd have used this technique if I'm dealing with smaller data set. But unfortunately I'm dealing with massive data set and it's around 500+ million rows. So using "IF" statement will completely slow down the performance. It's like Table Scan in SQL instead of Index Seek. But I really appreciate your time & effort, thank you mate.

                                                 

                                                I'm using one-to-one relationship between my Dimension & Expression fields and not performing any aggregation (i.e. just using Only() function). And pivoting them as columns for faster column selections. So users think they are making column selections but in turn they are just filtering the rows. This approach works great except the NULL rows will go missing for current selection and that means I won't see all the NULL valued columns. Hence I'm using this "Don't Filter" row as concatenated load in my Fact table and Dimension Tables to make sure users always see atleast one value pertaining to their selection.

                                                 

                                                I hope Stefan can do some magic!?

                                                 

                                                Cheers,

                                                DV