Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

JustinDallas
Valued Contributor II

Value in dimension disappears due to measure

TL;DR How do I make a dimension show when it's measure is null/undefined

Hello Folks,

I have a table with three dimensions (OrderId, Charge, ParentOrderId) and a Measure (=SUM({1<parentOrderId = P({$}orderId)>} charge)).  My data has a row whereby the ParentOrderId is null, and for whatever reason, that Order doesn't appear in my table unless I delete my measure.  Why is this?  I've never thought of it till now, so I assume it was just something I knew without asking questions.

RowCannibalism.PNG

My test data looks like so:

Orders:

//Getting a null while doing an inline Load is difficult,

//so I have to do magic like this

LOAD orderId, If(parentOrderId = '-', Null(), parentOrderId ) AS 'parentOrderId'

;

LOAD * Inline

[

orderId, parentOrderId

    1, '-'

    2, 1

    3, 1

]

;

Invoices:

LOAD * Inline

[

orderId, charge

    1 , 150

    2 , 50

    3 , 25

]

;

Any explanation is welcome.

1 Solution

Accepted Solutions
Digvijay_Singh
Honored Contributor III

Re: Value in dimension disappears due to measure

I think the measure is filtering that row, measure indicates only shows rows where parentid is equal to all possible order ids. Null in parentorderid is not a possible order id so that row is filtered.

If you write your measure like this -

SUM({<parentOrderId = P(orderId)>+<orderId = P(orderId)>} charge)

First part is same as yours one, the second part indicates, ALSO display all possible order Ids. and it shows all the rows but this may contradict your other needs.

4 Replies
YoussefBelloum
Esteemed Contributor

Re: Value in dimension disappears due to measure

Hi,

be sure that you have this checkbox checked (on the right pane, complements area)

include.png

rittermd
Honored Contributor

Re: Value in dimension disappears due to measure

Probably not showing because parentOrderId is null for that row.

If you don't have the box checked to show null values this row would not display.

Digvijay_Singh
Honored Contributor III

Re: Value in dimension disappears due to measure

I think the measure is filtering that row, measure indicates only shows rows where parentid is equal to all possible order ids. Null in parentorderid is not a possible order id so that row is filtered.

If you write your measure like this -

SUM({<parentOrderId = P(orderId)>+<orderId = P(orderId)>} charge)

First part is same as yours one, the second part indicates, ALSO display all possible order Ids. and it shows all the rows but this may contradict your other needs.

JustinDallas
Valued Contributor II

Re: Value in dimension disappears due to measure

". Null in parentorderid is not a possible order id so that row is filtered"


That was clearly common sense, but I couldn't see the forest for the trees.

Community Browser