topic Re: * Always show one row in Pivot Table * in QlikView

* Always show one row in Pivot Table *

IAMDV — Thu, 20 Jun 2013 07:57:44 GMT

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,

Re: * Always show one row in Pivot Table *

swuehl — Thu, 20 Jun 2013 08:33:05 GMT

Hi DV,

maybe like attached?

Re: * Always show one row in Pivot Table *

tresB — Thu, 20 Jun 2013 09:09:09 GMT

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 *

IAMDV — Thu, 20 Jun 2013 09:18:06 GMT

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,

Re: * Always show one row in Pivot Table *

IAMDV — Thu, 20 Jun 2013 09:23:13 GMT

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,

Re: * Always show one row in Pivot Table *

swuehl — Thu, 20 Jun 2013 09:55:53 GMT

Maybe like this?

Re: * Always show one row in Pivot Table *

Thu, 20 Jun 2013 10:24:15 GMT

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 *

IAMDV — Thu, 20 Jun 2013 10:37:50 GMT

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,

Re: * Always show one row in Pivot Table *

swuehl — Thu, 20 Jun 2013 12:41:40 GMT

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

Re: * Always show one row in Pivot Table *

IAMDV — Thu, 20 Jun 2013 16:10:18 GMT

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,

Re: * Always show one row in Pivot Table *

swuehl — Thu, 20 Jun 2013 16:23:31 GMT

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 *

IAMDV — Thu, 20 Jun 2013 16:59:59 GMT

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,

Re: * Always show one row in Pivot Table *

whiteline — Thu, 20 Jun 2013 20:08:54 GMT

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 *

IAMDV — Fri, 21 Jun 2013 08:52:11 GMT

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,

Re: * Always show one row in Pivot Table *

whiteline — Fri, 21 Jun 2013 09:07:12 GMT

Ah, so the goal is just to show all values, including nulls ?

Re: * Always show one row in Pivot Table *

IAMDV — Fri, 21 Jun 2013 09:10:28 GMT

That's correct. I just wanted to show all the values but I have removed those Null values in the process of using cross table. That's where I'm reducing the data volume. One last step away but some times the last logic step determines the whole effort.

Cheers,

Re: * Always show one row in Pivot Table *

Gysbert_Wassenaar — Fri, 21 Jun 2013 09:22:40 GMT

I don't see any way around this that doesn't involve either substituting all null values with real values or using an expression like only({$+1<Dimension_Field={'Dim Show Always'}>}Data) or a somewhat ugly Show All Values for the Attribute dimension + disabling Suppress Zero-values.

Re: * Always show one row in Pivot Table *

IAMDV — Fri, 21 Jun 2013 12:49:27 GMT

Many thanks Gysbert. I agree with you...it looks like I've hit the brick wall on this problem. However, I've a quick question on Set Analysis approach.

only({$+1<Dimension_Field={'Dim Show Always'}>}Data)

Can we write the set expression where it respects "Attribute" field in the above example. I mean let's say if I select Measure_01 & Measure_02 then Set expression should hide/exclude Measure_03. I think we can use Set Operators to get this working. Any thoughts?

Thanks,

Re: * Always show one row in Pivot Table *

IAMDV — Fri, 21 Jun 2013 15:37:28 GMT

Ignore me guys! I've got the Set Analysis Syntax. Many thanks again for all your support. I wish I could solve this one without Set Analysis i.e. without any additional CPU cycles. I'll update you guys if I come with a way of solving this one within script.

Thanks to Stefan, Whiteline & Gysbert.

Cheers,

topic Re: *** Always show one row in Pivot Table *** in QlikView

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

topic Re: * Always show one row in Pivot Table * in QlikView

* Always show one row in Pivot Table *

Re: * Always show one row in Pivot Table *

Re: * Always show one row in Pivot Table *

Re: * Always show one row in Pivot Table *

Re: * Always show one row in Pivot Table *

Re: * Always show one row in Pivot Table *

Re: * Always show one row in Pivot Table *

Re: * Always show one row in Pivot Table *

Re: * Always show one row in Pivot Table *

Re: * Always show one row in Pivot Table *

Re: * Always show one row in Pivot Table *

Re: * Always show one row in Pivot Table *

Re: * Always show one row in Pivot Table *

Re: * Always show one row in Pivot Table *

Re: * Always show one row in Pivot Table *

Re: * Always show one row in Pivot Table *

Re: * Always show one row in Pivot Table *

Re: * Always show one row in Pivot Table *

Re: * Always show one row in Pivot Table *