topic Re: Ignore one dimension in a pivot table in QlikView

Ignore one dimension in a pivot table

zermsms_ — Thu, 03 Dec 2015 15:06:38 GMT

Hello, I'm a qlikview newbie and looking for a solution for the following problem:

We have entries in a [simplified] PRODUCTS table for every product and production line [PRODUCTION_LINE_NAME], the initial product [identified by the PRODUCT_ID] is created in the AA lines where the following production steps are taking place in the further BB lines; the CC lines are finalizing the product. Each product will be transported during the factory using some kind of reusable bin [BIN], the tables are connected using the ID field. For one journey through the factory the life counter of a bin [BIN_LIFE_CNT] will be incremented. Some parameters are set initially on the first production stage AA, like the life counter, other parameters are added in later stages. The task is to show data for example from BB line together with the life counter from AA line for all products.

I hope the task is clear, how can this be achieved? Optional: Why do i loose my results when i select 'supress when value is null' on the dimension '=if (wildmatch(PRODUCTION_LINE_NAME,'BB*'),PRODUCTION_LINE_NAME)' [on the last table as shown below]

Re: Ignore one dimension in a pivot table

swuehl — Thu, 03 Dec 2015 18:32:47 GMT

If you suppress the dimension values for AA* production line, like in your last screenshot, you'll lose the records in your hyper cube that holds the data you are interested in, the life counter connected only to these prod lines.

You can think about adding logic to your script that fills every line in your BIN table with the appropriate life counter value.

Or, in your pivot table, try as expression

=Only( Aggr( Max(Total<PRODUCT_ID> BIN_LIFE_CNT), PRODUCTION_LINE_NAME, PRODUCT_ID))

Re: Ignore one dimension in a pivot table

zermsms_ — Fri, 04 Dec 2015 11:10:29 GMT

Thanks a lot Stefan,

I thought already about some logic on the scripting side to fill in the gaps – since this seems to be the correct way,

but currently I don’t want to touch the scripting too much since we are in the middle of a startup phase of a project.

Anyhow the expression

=Only( Aggr( Max(Total<PRODUCT_ID> BIN_LIFE_CNT),
PRODUCTION_LINE_NAME, PRODUCT_ID))

Is doing the job perfectly right.

So thanks a lot !

Have a nice day.

Best regards,

Markus