Solved: Dimension-wise visualization issue - Qlik Community

datanibbler · ‎2018-07-04

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

datanibbler · ‎2018-07-06

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 ...

View solution in original post

olivierrobin · ‎2018-07-04

hello

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

datanibbler · ‎2018-07-04

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?

olivierrobin · ‎2018-07-04

at this time, I don't see a solution

niclaz79 · ‎2018-07-04

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.

marcus_sommer · ‎2018-07-04

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

datanibbler · ‎2018-07-04

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?

marcus_sommer · ‎2018-07-04

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

datanibbler · ‎2018-07-04

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.

datanibbler · ‎2018-07-05

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