"if" condition in straight tables

[wanyunyang]

Hi all,

In the script, I have data like:

Item, Version, Shape, Qty

A, 2000, Round, 1

A, 2010, Square, 2

In the layout, I want to create a table like below:

Item, 2000 Version Shape, 2000 Version Qty, 2010 Version Shape, 2010 Version Qty

A, Round, 1, Square, 2

However, for "2000/2010 Version Shape", I used if(Version=2000, Shape) &if(Version=2010, Shape), then two rows show up, one null one with value, 2000 & 2010 opposite. I wonder what formula I should use for "2000/2010 Version Shape"?

[Happy_Mask_Salesman]

I'm not quite sure which desired output you want. But what I know for sure, is that you have to use set analysis Qlik technology to achieve it.

Set analysis allows you to select and filter data in you aggregation formulas.

Check this article: https://help.qlik.com/en-US/qlikview/May2023/Subsystems/Client/Content/QV_QlikView/ChartFunctions/Se...

Seeing the screenshot hints me that you would like to have Item column and Shape column, but the Shape column values must be selecting the Version 2000 and 2010.

You can achieve this with this formula:

Concat(DISTINCT {<Version={'2000', '2010'}>}Shape, ',')

This formula concatenates the values of 'Shape', but only those shapes whose Version are 2000 and 2010.

If you want only to show different shapes for an Item:

Put Shape as dimension, but Item as new expression:

Only({<Version={2000, 2010}>}Item)

Only() function gives you the only possible value from a field.  So, if you add Item 'B' with the shape of a Square, will fail, as it's not the only value possible for Square shapes..

I would suggest the first formula.

Regards,

[wanyunyang]

Hi there,

Thank you for your response. I would like my output to be like this:

Item, 2000 Version Shape, 2010 Version Shape

A, Round, Square

So basically I want two separate columns for "Shape": one for "2000 Version Shape", the other for "2010 Version Shape" (in one row, separate cells, so no concat). 

Your "only" expression hints me that I can use below functions to achieve this:

aggr(Only({<Version={2000}>}Shape),Item)

aggr(Only({<Version={2010}>}Shape),Item)

[Happy_Mask_Salesman]

In that case, you don't need the aggr(), just two individual expressions.

Expression 1, "2000 Version Shape"

Only({<Version={2000}>}Shape)

Expression 2, "2010 Version Shape"

Only({<Version={2010}>}Shape)

Other option, you can use a pivot table with two dimensions, Item and Version, then just 1 expression is needed:

Only(Shape)

For the screenshots im using QlikView, but its the same in Qlik Sense.

Regards,

[wanyunyang]

I don't know in qlikview, but in qlik sense, Only({<Version={2000}>}Shape) gives a measure, and aggr(Only({<Version={2000}>}Shape),Item) gives a dimension. That's why I need aggr here.

Thank you for helping me out 😉