topic Re: ValueList to match with multiple values in QlikView

ValueList to match with multiple values

jduluc12 — Sat, 16 Nov 2024 00:10:03 GMT

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

Re: ValueList to match with multiple values

marcus_sommer — Tue, 11 Aug 2020 10:59:59 GMT

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

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

- Marcus

Re: ValueList to match with multiple values

sunny_talwar — Tue, 11 Aug 2020 12:30:07 GMT

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

Re: ValueList to match with multiple values

jduluc12 — Tue, 11 Aug 2020 13:56:39 GMT

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.

Re: ValueList to match with multiple values

marcus_sommer — Wed, 12 Aug 2020 06:36:55 GMT

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

Re: ValueList to match with multiple values

tresB — Wed, 12 Aug 2020 07:13:43 GMT

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.