Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Preventing a dimension from being reduced, or still showing all the values

I cannot post the actual example for this question because all of it contains confidential information, so I will give a similar example.Please read the entire post and it should be relatively straightforward to see what I'm trying to do.

How do I prevent a specific dimension that is connected to reduced data from being reduced? Or, how do I work around that to show the missing values?

Suppose I have the following tables in my QVD. (I've kept them separate and shown only the relevant fields, for clarity.)

Orders

    OrderID

    OrderedAmt

    OrderedQty

    Customer

    OrderStatus

OrderStatuses

    OrderStatus

(In the QVD, Orders and OrderStatuses are connected by OrderStatus.)

Order contains --

    OrderID    OrderedAmt    OrderedQty    Customer    OrderStatus

    1              100                  10                  Acme              Open

    2              200                  20                  Acme              Complete

    3              300                  30                  MegaCo          Open

    4              400                  40                  MegaCo          Open

    5              500                  50                  LittleCo            Canceled

OrderStatuses contains --

    OrderStatus

      Open

      Complete

      Canceled

      Hold

The reason there is a separate OrderStatuses table is because I want to show all possible statuses even if there are no orders.

Using this design, a straight table showing totals, such as the one below is simple to make:

                    Open          Complete          Canceled          Hold          Totals

Amount          800              200                    500              0              1500

Quantity          80                20                      50              0                150

Here's the problem...

Suppose that I'm using Section Access and Strict exclusion. I've authorized users to specific Customers. If you are only authorized to Acme and MegaCo then when you open the document, your tables will look like the example below.


Order contains --

    OrderID    OrderedAmt    OrderedQty    Customer    OrderStatus

    1              100                  10                  Acme              Open

    2              200                  20                  Acme              Complete

    3              300                  30                  MegaCo          Open

    4              400                  40                  MegaCo          Open

OrderStatuses contains --

    OrderStatus

      Open

      Complete

As a result, the stratight table will look like the example below.


                    Open          Complete          Totals

Amount          800              200                  1500

Quantity          80                20                    150

Orders has been reduced, but so has OrderStatuses.

How can I enforce the reduction, but still show the user all the statuses. I want the straight table to contain every possible status, even if the user doesn't have any orders for them.

I've tried adding a MasterStatuses table that is not connected to the other tables and then use MasterStatus  as the primary dimension, with the actual OrderStatus as a secondary dimension, allowing nulls. That didn't work either.

Can someone please post the solution and/or a simple QVW that illustrates it? I'd like the resulting table to contain...

                    Open          Complete          Canceled          Hold          Totals

Amount          800              200                    0                   0              1000

Quantity          80                20                     0                   0               100

Thanks!

3 Replies
Highlighted
MVP
MVP

Just an idea, what if you add this to your data model script:

TMP:

LOAD OrderStatus Resident OrderStatuses;

JOIN (TMP)

LOAD Customer Resident Orders;

CONCATENATE (Orders)

LOAD OrderStatus, Customer Resident TMP;

DROP TABLE TMP;

I.e. Create each possible combination of Customer / OrderStatus without any real facts (OrderID, Amount, QTY etc will be NULL for these records). Reducing to certain customer values should then keep all OrderStatus possible.

Highlighted
Creator
Creator

Hi,

Pass a dummy value as either 0 or No against customer. If no orders are there then it will pick up dummy value.

Regards,

Imran K

Highlighted
Not applicable

I'm not sure what you mean by "Pass a dummy value". Could you explain that, please?