ValueList to match with multiple values

[jduluc12]

Hi,

I have a situation where I need to write a valuelist expression something like this

Dimension:

ValueList('S', 'P')

Expression:

If ( valuelist('S', 'P') = ('SA', 'ST'), SUM(AMT), if (valuelist('S', 'P') = ('PA', 'PT'), SUM (QNT)) )

so I need to match the 'S' with two values ('SA', 'ST') and 'P' with ('PA', 'PT').

is it possible?

thanks

Jean

[marcus_sommer]

If it's already your real-case the following simplifying should be enough:

If(valuelist('S', 'P') = 'S', SUM(AMT),  SUM (QNT))

- Marcus

[sunny_talwar]

What is SA and ST here? Valuelist is only S or P... is there another dimension in this chart?

[jduluc12]

What I want to achieve here is grouping of certain entities.

The real entities are SA, ST, PA and PT.

What I need to do is to have a synthetic dimension which groups SA,  ST in S and PA, PT in P.

I do not know if there is anything in Qlik which can do that. 

The closest function which I could think of was ValueList so I used it to express my need.

I hope it is clear now.

[marcus_sommer]

The use of valuelist() is often the most unsuitable way of creating a (part of a) datamodel. The recommended way is to associate all dimensions directly within the script/datamodel. Should this really not possible you could create such synthetic dimensions also within the datamodel as loosen tables (tables which have no further connection to other tables) for example with simple inline-statements because it's more flexible and easier to handle within the UI as with a valuelist().

- Marcus

[tresesco · Accepted solution]

Looking into your sample data and requirement, it seems that you could use a calculated dimension instead. 

Calc Dim : Left(Entity, 1)

Exp : Sum(QNT)

You have to find a pattern for calculated dimension if this doesn't exactly solve your case issue.