Related tables issues

[SerSwagster]

Hi everyone,

this is my issue on QlikView: i have two tables showing data about the sales of a retail chain, the first one related to the north of a country and the second one to the south. The dimension used for each table is the category of the product (shoes, smartphones, food, etc.), while the measures are related to the sales (for the current month, previous, delta etc).

When I select a certain category from one of the two tables, I want that the same category is selected and shown also in the other table; actually, it happens only if , in the second table, at least one of the measures associated to the selected category is not null. My aim is to show the selected category in the second table even if all its measures are null.

This is an example: i click on the category shoes in the first table (related to the sales of the north); if shoes category for the south discounts has at least one of the measures not null, the shoes row will be shown in table two, otherwise it will not happen. I want to show the selection made on the first table also in the second, even if all its measures are zero.

Any suggestions?

[Or]

It sounds like the most logical approach would be to simply merge these two tables, with an added field of 'North' or 'South' to identify the source. You could do this using JOIN or CONCATENATE in your script.

[SerSwagster]

No, I have to mantain this structure due to requirements.

I thought the problem was related to the checkbox of the properties of the table, like including null or missing values, but the result is still the same.

[Or]

Your requirements specify that your app must have a certain data structure? That's unusual - typically the structure is made to match the display requirements within the Qlik framework, rather than requiring a specific table structure be adhered to. I suppose that doesn't help us here, though...

If you're required to keep this structure, the question is how the tables are joined. If they are joined on all common fields, then any field selection (on a common field) would automatically register in both tables. However, if you have a specific key set up between the tables, then selecting a value in e.g. Category = Shoes in Table1 will narrow down Table1 to only values that are Category = Shoes, and will then fetch only values from Table2 which match the keys for those values.

You could possibly work around this by using set analysis, but that's only likely to be feasible if there's a fairly small number of filters involved.

[SerSwagster]

Maybe i misspoke, ì'm sorry. I have two straight tables, with the same columns (dimension: category, measures: current month, previous month, delta) and, through a set analysis, one take data from the north discounts' and the other from the south. Nevertheless, although the category values are in common between the two tables, in some cases for a specific category all the measures gives back zero as result; this implies that selecting the specific category in the other table (one with measures not null), the second one gives back no values, while I would retrieve the same category row with all the measures equal to zero.

So it's not a problem of filtering, in my opinion, because the filter seems to work, but a problem of displaying the result. Qlik seems to cover the row if all the values are zero, while I want to display the selected row even if all its measures are zero.

[Or]

In that case, I would suggest checking your arithmetic first. If you're performing mathematical actions based on fields that are null, you'll get null as your result. You should ensure that any values you are performing mathematical actions on are either encased in an aggregation function such as sum(), or you can use e.g. Revenue * 1-(Alt(Discount,0)) to ensure no nulls are used.