Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

vidyasagar159
Contributor II

How to Pass Condition values into ValueList()

Hi All,

How to achieve the below expression in a right way.

Expression:

Valuelist('Apple','Bannana','Carrot',if(wildmatch([fruits],'*G*'),[fruits]))

Thanks,

-Vidya

1 Solution

Accepted Solutions
pradosh_thakur
Honored Contributor II

Re: How to Pass Condition values into ValueList()

script change

load * inline [

dim

1

2

3

];

dimension

=pick(dim,'Average TOP 3 Products by Sales Amount','Average TOP 3 Products by Actual Amount',if(wildmatch(Product,'*E*'),Product))


expression

PICK(dim, Avg({<Product={"=rank(sum([Sales Amount]),Product)<=3"}>}[Risk Value]),Avg({<Product={"=rank(sum([Actual Amount]),Product)<=3"}>}[Risk Value]),SUM([Risk Value])
)

Learning never stops.
9 Replies
prma7799
Honored Contributor III

Re: How to Pass Condition values into ValueList()

pradosh_thakur
Honored Contributor II

Re: How to Pass Condition values into ValueList()

where are you using this and what is the exact requirement of the below expression? there may be a easy way out.


if you are using it in the dimension try this


in the script

load * inline [

dim

1

2

3

4

];


in the dimension

pick(dim,'Apple','Bannana','Carrot',if(wildmatch([fruits],'*G*'),[fruits]))

Learning never stops.
vidyasagar159
Contributor II

Re: How to Pass Condition values into ValueList()

Hi Pradosh,

With the expression, i was able to achieve the below result set.

Table:

  

ProductSales AmountActual AmountRisk ValueReturn Value
A6052015
B501002525
C70705535
D20351530
E10251715
F530205

Dimension:

=ValueList('AUM','Flow')

Measure:

if(ValueList('AUM','Flow') =

'AUM', Avg({<Product={"=rank(sum([Sales Amount]),Product)<=3"}>}[Risk Value]),

if(ValueList('AUM','Flow') =

'Flow',Avg({<Product={"=rank(sum([Actual Amount]),Product)<=3"}>}[Risk Value])

))

Result:

  

DimensionRisk Vlaue
Average TOP 3 Products by Sales Amount33.33
Average TOP 3 Products by Actual Amount31.66

Here is it trick:

Now I want to show my own product in the result table.

Example:

  

DimensionRisk Vlaue
Average TOP 3 Products by Sales Amount33.33
Average TOP 3 Products by Actual Amount31.66
E17

Thanks,

-Vidya

Luminary
Luminary

Re: How to Pass Condition values into ValueList()

Hi,

ValueList() only accepts discrete values, so you cannot use If inside Valuelist(). Try fixing this on data model level by creating a separate table linked to your data which will contain those "fruits" you need and will connect to respective values in your data model.

Hope this helps.

Juraj

pradosh_thakur
Honored Contributor II

Re: How to Pass Condition values into ValueList()

Is this what you are looking for?

Capture.PNG

Learning never stops.
pradosh_thakur
Honored Contributor II

Re: How to Pass Condition values into ValueList()

script change

load * inline [

dim

1

2

3

];

dimension

=pick(dim,'Average TOP 3 Products by Sales Amount','Average TOP 3 Products by Actual Amount',if(wildmatch(Product,'*E*'),Product))


expression

PICK(dim, Avg({<Product={"=rank(sum([Sales Amount]),Product)<=3"}>}[Risk Value]),Avg({<Product={"=rank(sum([Actual Amount]),Product)<=3"}>}[Risk Value]),SUM([Risk Value])
)

Learning never stops.
vidyasagar159
Contributor II

Re: How to Pass Condition values into ValueList()

Hi All,

I have found the solution. Thanks to everyone and special thanks to Juraj for giving me this idea.

Step1: Created Inline Table with column names DimID, DIM values

Step2: Created Resident with only the products which I was interested in.

for example:

[Performance Statistics]:

load if(WildMatch("Furits",'*E*'),"Product ID") as Dim,if(WildMatch("Fruits",'*E*'),"Product Name") as DimValue Resident [Fruits_Table];

Step3: Concatenated Resident with Inline table.

Now that I have my table ready. I used the following expression to achieve my requirement.

Dimensions:

=if([Product ID]=Dim,DimValue,

if(Dim=1,DimValue,

if(Dim=2,DimValue,

if(Dim=3,DimValue

))))

Measures:


if(Dim=1 , Avg({<[Product ID]={"=rank(sum([Sales Amount]),[Product ID])<=10"}>}[Risk Value]),

if(Dim=2 , Avg({<[Product ID]={"=rank(sum([Actual Amount]),[Product ID])<=10"}>}[Risk Value]),

if(Dim=3 , Avg({<[Product ID]={"=rank(-sum([Actual Amount]),[Product ID])<=10"}>}[Risk Value]),

([Risk Value])

)

))

Result:


DimensionRisk Value
Average TOP 3 Products by Sales Amount33.33
Average TOP 3 Products by Actual Amount31.66
E17

Thanks,

-Vidya

pradosh_thakur
Honored Contributor II

Re: How to Pass Condition values into ValueList()

Your way is one way of achieving it my friend. But if you use pick() with no nested if it will be optimized and faster as well.

Good that you found a solution.

Learning never stops.
vidyasagar159
Contributor II

Re: How to Pass Condition values into ValueList()

Thanks, Pradosh.

Your logic is much better than mine in both performances and the expression building.Mine has some performance issues.

Thanks,

-Vidya

Community Browser