topic Re: Using ValueList with Pick(Match( in QlikView

Using ValueList with Pick(Match(

data_guru_001 — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I am trying to create a graph from my existing data, but am having trouble in doing so. Here are the details.

I have a table that looks like this (not exactly, but the same idea for simplicity):

I am trying to create a line graph, that puts sum(WK1_AMT), sum(WK2_AMT), & sum(WK3_AMT) on the same trend line. Just like this:

As you can tell, its not so simple, since each amount represents its own column header/expression. My idea, and this is where my question lies, is if I can utilize PICK(MATCH(VALUELIST... where I create a value list of something like ValueList('WK1','WK2','WK3').

Then, in my expression, write something like:

PICK(MATCH(VALUELIST('WK1','WK2','WK3'),'WK1','WK2','WK3'),

sum(WK1_AMT),

sum(WK2_AMT),

sum(WK3_AMT)

))

Essentially, what I am trying to do, is force a manually created dimension value, to an expression. Trying to do the above, my graph keeps saying "No Data to Display", so I'm not sure if I am just writing it incorrectly, or if this just cannot be done (I did already add =Valuelist('WK1','WK2','WK3') in the calculated dimension.

Any help with trying to create the above graph, from the existing table would be greatly appreciated. I'm sure a suggestion of utilizing a crosstable would come up, but there is actually a lot more data in my table that would make that complicated. The above table is a simple idea of what I have.

Thank you all

Re: Using ValueList with Pick(Match(

pooja_prabhu_n — Fri, 27 Jul 2018 05:42:27 GMT

Hi,

Instead of Pick and match you can try if statement

=if(ValueList('WK1_AMT','WK2_AMT','WK3_AMT')='WK1_AMT', Sum(WK1_AMT),

if(ValueList('WK1_AMT','WK2_AMT','WK3_AMT')='WK2_AMT', Sum(WK2_AMT),

if(ValueList('WK1_AMT','WK2_AMT','WK3_AMT')='WK3_AMT', Sum(WK3_AMT))))

Please refer the attached qvw file

Thanks,

Pooja

Re: Using ValueList with Pick(Match(

BalaBhaskar_Qlik — Fri, 27 Jul 2018 10:29:22 GMT

May be this:

=if(ValueList('WK1','WK2','WK3')='WK1', Sum(WK1_AMT),

if(ValueList('WK1','WK2','WK3')='WK2', Sum(WK2_AMT),

if(ValueList('WK1','WK2','WK3')='WK3', Sum(WK3_AMT),0)))

Re: Using ValueList with Pick(Match(

data_guru_001 — Fri, 27 Jul 2018 20:36:39 GMT

Hi,

Thanks for the response. However, I am trying to avoid using repeated IF statements, as because I have so much data in the table, it would really drag down the performance issues.

Any idea with a workaround on the IF statements?

Thank you

Re: Using ValueList with Pick(Match(

Neymar_Jr — Sun, 29 Jul 2018 04:03:58 GMT

Hi,

PFA the app with pick-match used in expression.

Thanks,

Re: Using ValueList with Pick(Match(

fabdulazeez — Sun, 29 Jul 2018 04:08:25 GMT

PICK(MATCH(VALUELIST('WK1','WK2','WK3'),'WK1','WK2','WK3'),

sum(WK1_AMT),

sum(WK2_AMT),

sum(WK3_AMT)

))

I extra parentheses closing

Re: Using ValueList with Pick(Match(

data_guru_001 — Mon, 30 Jul 2018 23:39:37 GMT

This is it. Thank you!