topic Re: Data access problem (2 main tables in dashboard) in QlikView

Data access problem (2 main tables in dashboard)

Anonymous — Mon, 11 Sep 2017 18:56:13 GMT

Hello,

Have a problem with implementing data-access (did not work much with data access or data reduction so far, so please bear with me a little).

Scenario is: Got a dashboard with two main tables.

Table one is a very generic Stocks table (part_id, part-number, description, quantity, serial, warehouse, stock country).

The second is a Sales Summary table (customer, sales date, part_id, sales amount, currency, salesperson, sales country).

Link between the two is the part_id (unique identifier for a given part-number).

This allow a user to see from a stocks perspective what were the Sales and from the Sales perspective what stock there is.

So the link is fine and that's just what I need.

However, both tables also got a country dimension.

The dimensions are named differently in each table, so the link is just the part_id - BUT this is the domension that needs to be used for data-access: users from different countries ONLY need to see stock AND Sales from their respective countries.

Have seen a number of posts / articles on this subject but none seem to be exactly what I'm trying to do (could be because I'm trying to do this wrong...).

Did manage a lock me out of the dashboard a couple of times today though... glad for backups

Also run into loops, or either Stocks or Sales wouldn't show any data

Can data-access be applied to two separate tables using a specific dimension while the tables are linked through a different dimension? How?

Could anyone point me to a article/post on topic?

Big Thanks

Re: Data access problem (2 main tables in dashboard)

marcus_sommer — Tue, 12 Sep 2017 11:49:30 GMT

To control the access on certain data to certain users you could use Section Access. I'm not sure how to implement it in your particular case of two fields linked per another field because my scenarios are a bit different. In general it should be possible but you might need a few trials to find the right solution.

A bit more simpler could the section access be if you changed a bit your datamodel. I could imagine that a concatenation of part_id, quantity and stock country (as country) to the sales table would bypass the problem with two different country-fields. This meant you would get a single fact-table and the other fields from the stock table could be loaded (distinct) as a dimension-table.

- Marcus

Re: Data access problem (2 main tables in dashboard)

prieper — Tue, 12 Sep 2017 12:03:58 GMT

Would suggest to link the countries to the part_id:

AccessTable:

LOAD DISTINCT part_id, UPPER(stock_country) AS Country RESIDENT Stocks;

CONCATENATE LOAD DISTINCT part_id, UPPER(sales_country) AS Country RESIDENT Sales;

and then place your security on the field Country,

i.e. a person being able to see a certain country would be allowed to see all part_id's, be it in stock in this country or being sold there.

Peter

Re: Data access problem (2 main tables in dashboard)

Anonymous — Tue, 12 Sep 2017 19:39:34 GMT

Hi Marcus, Hi Peter

Many thanks for your replies, you were life-savers

Went with Peter's suggestion and created a AccessTable with part_id + country. Works 99.99% ok. Only unexpected side-effect for me is that Qlikview does a OR in Sales and Stock Country, so a user having access to France only, can still see stock existing in the UK if that part_id has been used in a French Sales Order.

Fortunately, these cases are rare and this behavior actually improves the synergy between Sales and Logistics so will be seen as a plus.

If this ever becomes an issue, think there's a "nuclear" option to sever the part_id link between Stock and Sales.

Users would have to check Sales and Stock data separately, which would be a loss of functionality but...

In the long-run Marcus suggestion would be the ideal - changing the data-model, but not possible to implement due to time constraints (may be for a future v2.0 dashboard...)

P.S. All-in-all these problems what you get when trying to quickly re-ash an old dashboard that was used in the UK only, to be used in all of Europe -- there's just not such a thing as a free lunch

Again a big thank you to both!