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

[IAMDV]

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

[swuehl]

Hi DV,

maybe like attached?

[tresesco]

Hi Deepak,

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

Thanks.

[IAMDV]

Hi Stefan,

Thank you for quick response. I should have given more information (Sorry about that!). I followed the GENERIC LOAD approach (Same as yours), however I have more than one dimension tables which are linked to the Fact Table. Please attached the QVW where Dimension_Field2 is not returning possible/associated values when I select "Dimension_Field to Select on" and vice-versa. The reason is obvious that we are duplicating another set of rows for each "Dim Show Always" value in both the Dimension tables. And I have 4 Dimension tables. I hope this makes sense. So how do I solve this problem?

Ps: I don't want to duplicate values in my FACT table because it's massive data set.

Thanks gain for your time & help.

Cheers,

DV

[IAMDV]

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

[swuehl]

Maybe like this?

[#7]

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

[IAMDV]

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

[swuehl]

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

[IAMDV]

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