topic Re: Using GetFieldSelections to call values in a field in QlikView

Using GetFieldSelections to call values in a field

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

I'm very new to Qlikview, so I'm sure this question is very easy to solve. I'm creating a pivot table where the user can choose what kind of measure they want to see in the table. There are 8 different measures the user can choose from. Right now, I'm using the following syntax:

If(Measure='MYTD',[MYTD],
If(Measure='LC',[LC],
If(Measure='Rep',[Rep],
If(Measure='R',,
If(Measure='Ratio',[Ratio],
If(Measure='Spend',[Spend],
If(Measure='Wa',[Wa])))))))

Is there a way I can use GetFieldSelections to write all these in 1 line and not specify the name of each measure (in case these names change in the future)?

Re: Using GetFieldSelections to call values in a field

nishanthi_8 — Tue, 17 Jul 2018 05:31:06 GMT

Go for pick and match. It works like this:

Pick(Match(Measure),'MYTD','LC','Rep','R',''Ratio,'Spend','Wa'))

Re: Using GetFieldSelections to call values in a field

sunny_talwar — Tue, 17 Jul 2018 11:59:17 GMT

Why don't you try this... assuming that you will only select one Measure at a time

$(=Measure)

Re: Using GetFieldSelections to call values in a field

marcus_sommer — Tue, 17 Jul 2018 12:06:05 GMT

The suggestion from nishanthi.8 simplifies your task whereby the syntax isn't quite correct and it should be rather look like:

Pick(Match(Measure,'MYTD','LC','Rep','R','Ratio','Spend','Wa'),

[MYTD],[LC],[Rep],,[Ratio],[Spend],[Wa])

Another method - at least if your listbox-values are identically with your fieldnames - would be to use a $-sign expansion like: [$(=only(Measure))]

- Marcus

Re: Using GetFieldSelections to call values in a field

Anonymous — Tue, 17 Jul 2018 13:39:57 GMT

Thanks! This was helpful, but as Marcus mentioned the syntax is not complete. I need to tell the Pick function what to pick for every match.

Re: Using GetFieldSelections to call values in a field

Anonymous — Tue, 17 Jul 2018 13:46:50 GMT

Thanks Sunny! Should I use this within another function?

Re: Using GetFieldSelections to call values in a field

Anonymous — Tue, 17 Jul 2018 13:47:34 GMT

Thank you Marcus! Your pick and match function worked perfectly. I'm not sure how to use the $ expansion though.

Re: Using GetFieldSelections to call values in a field

sunny_talwar — Tue, 17 Jul 2018 15:30:18 GMT

Did you try this at all?

$(=Measure)

[$(=only(Measure))]

Re: Using GetFieldSelections to call values in a field

sunny_talwar — Tue, 17 Jul 2018 15:35:07 GMT

All you need to do is to replace this

If(Measure='MYTD',[MYTD],
If(Measure='LC',[LC],
If(Measure='Rep',[Rep],
If(Measure='R',,
If(Measure='Ratio',[Ratio],
If(Measure='Spend',[Spend],
If(Measure='Wa',[Wa])))))))

with

$(=Measure)

Re: Using GetFieldSelections to call values in a field

sunny_talwar — Tue, 17 Jul 2018 15:36:15 GMT

So, for instance if you were doing this previously

Sum(

If(Measure='MYTD',[MYTD],
If(Measure='LC',[LC],
If(Measure='Rep',[Rep],
If(Measure='R',,
If(Measure='Ratio',[Ratio],
If(Measure='Spend',[Spend],
If(Measure='Wa',[Wa])))))))

)

Change it to this

Sum($(=Measure))

Re: Using GetFieldSelections to call values in a field

Anonymous — Tue, 17 Jul 2018 16:49:40 GMT

Unfortunately it didn't work.

Re: Using GetFieldSelections to call values in a field

nishanthi_8 — Fri, 20 Jul 2018 05:25:54 GMT

Yes Marcus you are right, I just got messed up with "Brackets". Thanks for the suggestion.