Qlik 12.10 dimension/expression values

[Opp]

Hi

I have a a list of product groups under dimension "groups". I would like to have all these ´groups corresponding to a certain value. Let's say value "brown products" to have value of 2.05; "white products" to be replaced with value 4.32 etc.

As i am not a QV expert, I would come up with something like "IF(GROUP='brown products','2.05'; GROUP='white products','4.32' etc). Which is the correct way to write this command?

[thiago_mlg]

@Opp The best solution is to create a new field when loading this data. Use the following code in the script when loading the table.

IF(GROUP='brown products',2.05,

IF(GROUP='white products',4.32)) AS GROUP_VALOR

And use this new field in the chart in question.

But like @p_verkooijen said below, if the number of products is large, performance will be greater if done using a mapping table.

I hope I helped.

[Opp]

Sorry, what?

[p_verkooijen]

@Opp  If your list is long, better create a mapping table (Excel or an INLINE Table)

For example with a INLINE Table.

MapGroupNumber:
MAPPING LOAD * INLINE [

GroupName, GroupNumber
brown products, 2.05
white products, 4.32

];

Products:
LOAD
  *,
  APPLYMAP('MapGroupNumber', GROUP) AS GroupNumber
FROM products.qvd (QVD);

Easier than having to create a big nested IF, and easy to use. If there is no match the original GROUP name will be visible in the GroupNumber field and you know what new groups to add to the mapping table.

[Opp]

Thank You. Any chance you could present me that in a way that I can copy that whole script and replace the corresponding values with my actual values?

[p_verkooijen]

Hi @Opp 

That is exactly what my response was, you can copy paste this in a QlikView script.

You need the INLINE table and APPLYMAP line.

As you are working with QlikView you can edit the Inline table when you click the "hammer" button

[Opp]

Sorry, this is beyond my knowledges. I have no idea what an inline table and applymap line are. 

Apparently there is limit for IF function (99 lines). Tested with copy-paste, before wasting time on creating the formula.