topic Re: Dimension-wise visualization issue in QlikView

Dimension-wise visualization issue

datanibbler — Wed, 04 Jul 2018 09:04:16 GMT

Hi,

I have a barchart where I have to display values from two different tables - the actual values from one, plan values from another.

The table with the actual values has a dimension called "Realization" which is used in the chart.

In the other table I have created a lookalike, but called it differently to avoid the creation of a synthetic key and a loop-link.

For that reason I cannot properly align the bars for the plan values next to the actual values in the chart. I cannot just use another dimension, either.

How can I now make the bar showing the planned values next to the correct one according to this categorization (I have that value in the table with my plan_values, only the fieldname is different)?

Thanks a lot!

Best regards,

DataNibbler

Re: Dimension-wise visualization issue

olivierrobin — Wed, 04 Jul 2018 09:16:25 GMT

hello

why don't you join your 2 tables, so you have the 2 columns in the same table, with the same dimension ?

Re: Dimension-wise visualization issue

datanibbler — Wed, 04 Jul 2018 09:28:33 GMT

Hi Oliver,

that would be a possibility. The granularity is not the same, so if I join by all the fields I have in my planning_table, I will by default multiply the values - the same value will be matched to many lines.

Well, I could use my favourite, mapping, but then I'd have to do it twice, to and fro, and only the first line (sorted according to the dimensions I have in my planning_table) would get a value and the sum would be correct.

Or I could sort the table with the actual values, create a line_ID within every "group" and match a plan_value only to the lines with a nr. 1. Then again the sum would be correct.

Looks like a bit of effort, but that's possible.

Is there another, maybe quicker way to achieve this?

Re: Dimension-wise visualization issue

olivierrobin — Wed, 04 Jul 2018 09:34:03 GMT

at this time, I don't see a solution

Re: Dimension-wise visualization issue

niclaz79 — Wed, 04 Jul 2018 09:34:04 GMT

Hi, you don't need to join. I would suggest concatenate() with the fields that are the same (date, groupings etc) instead. Concatenate works fine in most cases where you may fear getting extra lines. Only in rare instances (extremely large data sets) will you be absolutely required to do a join.

Re: Dimension-wise visualization issue

marcus_sommer — Wed, 04 Jul 2018 09:35:25 GMT

IMO the easiest way to get sales and budgets together is just concatenating the tables - no (respectively lesser) problems with missing values on either side or a different granularity.

- Marcus

Re: Dimension-wise visualization issue

datanibbler — Wed, 04 Jul 2018 11:09:07 GMT

Hi Marcus,

okay, I will try that. There are a few fields I just don't have in my plan_table, I will just omit them for now, that should work nonetheless. And I keep my plan_values in different fields to avoid summing them up together with the actual values in the GUI. I will add the key and rename it so that I have one keyfield throughout the table, only the contents will differ. And to be sure I will add a field highlighting the source of records in case there should be any trouble on the GUI - I have not really worked with concatenating different tables before, that's also why I'm sceptical about LinkTables. I'm not sure what will happen in the tablecharts there are on the GUI, to view details. Most of the fields in the table are in the tablecharts as dimensions, so I guess I will just have to check the "suppress zero values" for every one - well, actually I should not display the plan_values in detail tables at all I guess, it just doesn't smell correct.

Have I forgotten anything?

Re: Dimension-wise visualization issue

marcus_sommer — Wed, 04 Jul 2018 11:39:44 GMT

Currently I don't remember any significantly disadvantage by using a concatenated fact-table. Neither by creating UI objects nor by the performance even if the tables are quite heterogeneous which results in a lot of NULL's within the table. For me it's the standard approach to build a datamodel and I use other approaches only if it's absolutely necessary.

I think the following will be helpful for you:

Concatenate vs Link Table

Fact Table with Mixed Granularity

- Marcus

Re: Dimension-wise visualization issue

datanibbler — Wed, 04 Jul 2018 11:46:20 GMT

Thanks!

I think I have read both, but I will have another look.

Ah, the second one is seemingly just what I need here. so I will have a closer look at that.

Re: Dimension-wise visualization issue

datanibbler — Thu, 05 Jul 2018 07:38:54 GMT

Hi Marcus,

well, I have read that and I think I understand the concept. It seems to be the better option as opposed to joining the budget values just to select lines in that fact_table and leaving the others empty.

I'm doing this for the first time though, so the effort for this will not be much less than that for the other option 😉

Also, it will change my datamodel. Not a big issue though.

My first issue with this is the creation of generic keys: I have understood this concept, but - in my fact_table I have a productID, but for the budget values I don't have anything about the products, I have a CompanyCode, a customer and a ProfitCenter - and I cannot group the products into ProfitCenters ... what to do there? Can you think of anything to do in that respect?

Thanks a lot!

Best regards,

DataNibbler

Re: Dimension-wise visualization issue

datanibbler — Thu, 05 Jul 2018 08:03:58 GMT

So - my budget values are not grouped by product in any way, Year and month in the budget table stand for the exact OrderDate, ProfitCenter and CompanyCode and CustomerID are the same.

So I guess I will have to make a key that is unique in my budget_table and map that against a key that is unique in my fact_table, no - the complete key, not its parts.

Well, that will be a bit, and I'm still not sure about whether to go that way at all - with the other method, albeit potentially less advantageous in the future, I cannot tell yet, I could well find my way alone. And the key in the fact_table would remain unique, though that's just for the looks ...

Re: Dimension-wise visualization issue

marcus_sommer — Thu, 05 Jul 2018 08:05:03 GMT

Hi DataNibbler,

in this case you could only compare (and display) sales and budgets on a higher level as productID - this is independent from the used datamodel because it's caused from missing data respectively missing matching data-levels.

An alternatively would be to distribute the aggregated data on the lower levels by populating extra data with any artificial logic maybe by taking the available productID's and adding them to the budget data with a measure like:

1 / count(distinct productID)

but IMO it makes often not much sense.

- Marcus

Re: Dimension-wise visualization issue

datanibbler — Thu, 05 Jul 2018 08:55:33 GMT

Hi Marcus,

that's just the point. Somewhere I'd have to fake something.

I think for starters I'll just try doing what I know and keeping it altogether separate - then in the chart, instead of using that field as dimension, I'd have to create an inline table with essentially the same values and use this as dimension and use some set_analysis to display the right values on the right dimension_points - that way I can actually put apples and oranges side by side, whatever I want, I've done that before.

Apart from this one table, I have no issue with the budget values, just this one has one more dimension in the chart that's making trouble.

Going forward I'll revisit this, seeing whether this requirement becomes repetitive.

Best regards,

DataNibbler

Re: Dimension-wise visualization issue

datanibbler — Thu, 05 Jul 2018 09:17:31 GMT

Oh, that's cool - just not now 😉

I have now created an inline table with essentially the same values as the field that was formerly used as a dimension, used this as dimension instead and made two copies of the actual values - one for each one of the two distinct values in there - and put my budget value in the middle - but now QlikView stacks the budget value on top of the actual values with the same dimension 😉

Let's see if that can be fixed, else I'll have to go the way you proposed after all. That also seems possible.

Re: Dimension-wise visualization issue

datanibbler — Thu, 05 Jul 2018 10:58:27 GMT

Okay - I got that fixed, but it has a pricetag so to speak - I now have double the nr. of values in the legend - one each for "Current" and "Following".

I had to introduce, instead of a field with two distinct values that was formerly used as dimension, an inline table with four values, two each for "Current" and "Following", that way I can have two different bars display side by side within that "group".

Let's see what people say to that - just to have a possible alternative, I'll try out the other way, too.

P.S.: To append that budget data where I have no product-related dimension, I'll just have to make one key for the budget_data containing for instance Year, Month, CompanyCode and Prctr and one for the actual values containing Year, Month, Day, CompanyCode, Prctr and ProductID and then map one to the other.

Ideally - as a matter of fact, the key to the actual values in that fact table is built quite differently and does not contain anything product-specific, either. I will have to try whether I can build a unique key in that way.

P.P.S.: Well, not quite - I do need a few other elements to build a unique key - but the concept would work I guess.

P.P.P.S.: But doesn't that affect performance? That is an extremely long key now ...

Re: Dimension-wise visualization issue

datanibbler — Thu, 05 Jul 2018 13:40:51 GMT

Hi,

so now I have my generic key with a | in the middle - it kind of looks like this:

>>> [Key_budget] | [Key_actual] <<< (for the actual values) and

>>> [Key_budget] | NULL() <<< (for the budget values)

whereby the[Key_budget] is about the first half of the [Key_actual].

<=> Now I have to have a unique key t link that "dimensional link table" to my actual central Linktable - but on the other side? Do I need to create two keys to simultaneously link to one actual value and to a budget value?

In two fields or in one? I guess it must be two fields, no? That would necessarily lead to the creation of a synthetic key, though.

Can you help me a little bit with this?

Thanks a lot!

Best regards,

DataNibbler

Re: Dimension-wise visualization issue

marcus_sommer — Thu, 05 Jul 2018 14:02:13 GMT

I agree with the suggestion from HIC to avoid a linktable in this case: https://community.qlik.com/blogs/qlikviewdesignblog/2012/10/26/mixed-granularity#comment-68077.

- Marcus

Re: Dimension-wise visualization issue

datanibbler — Thu, 05 Jul 2018 14:31:17 GMT

Hmmm ... the issue is, the unique key for the actual values consists partly of the unique key for budget values - but it does contain a few more elements, like four or five - so if I was to use just the first part as the key to my central LinkTable, that would link not to one actual and one budget value, but to a whole lot of actuals and one budget value.

So I need that inbetween-link-table with double the nr. of lines that I have in my fact table - to have every key twice, once for exactly one actual value and once more for the corresp. budget value.

I could probably push that "translation" into the central LinkTable, but that would double its nr. of lines - and that one has more than two columns and it links to all the other tables - logically - so there is no knowing what effects it might have if I double the nr. of rows ...

Re: Dimension-wise visualization issue

datanibbler — Fri, 06 Jul 2018 06:38:37 GMT

Well, since the LinkTable is a fact that I cannot get rid of easily I guess that generic keys are not the best way to go in my situation. I will rather keep budget_data separate and mess around a bit in those few charts where I need to display it alongside fact_data - in most cases, that is easily possible. I had only that one chart, maybe two or three, where I could have well used the common dimension so having the budget_data in my fact_table seemed a good idea - seems like that would be a bit over the top to possibly mess up the entire datamodel ...