topic How to code Field name in Set expression Dynamically? in QlikView

How to code Field name in Set expression Dynamically?

Fri, 14 Nov 2014 12:24:11 GMT

Hi All,

I am trying to write a set expression in such a way that my field name sahould change dynamically based on Dimension

Example:

Expression : sum({<Flag_1={'1'}>}Sales)

I have created an Inline table which has no link with my dashboard and it has values like 'Flag' = 1,2,3,4,5,6,7,8,9,10.... so on till 100

Here is my data:

Purchase Year	Sales	Flag_1	Flag_2	Flag_3	Flag_4	..........	Flag_100
2008	20	1	0	0	1		1
2009	400	1	1	1	0	0	0
2010	4547	0	0	1	1	0	0
2011	2563	1	0	0	0	1	1

and I want values only for Flag_1.....100 = 1

Want to create a line chart with Flag as dimension and based on my dimension values my field 'Flag_' should change

for example:

sum({<'Flag_' & Flag={'1'}>}Sales)

doing this I can easily avoid nested if condition, but the above expression is not working at all.

Any help will be highly appreciated

Rob Wunderlich

Steve Dark Massimo Grossi Miguel A. Baeyens

Re: How to code Field name in Set expression Dynamically?

alexandros17 — Fri, 14 Nov 2014 12:35:01 GMT

Use a pivot table then drag your flag dimension to the top right ...

Re: How to code Field name in Set expression Dynamically?

jonathandienst — Fri, 14 Nov 2014 12:35:10 GMT

I would crosstable load this data so that it is truely dynamic. Something like:

CROSSTABLE (FlagType, FlagValue, 2)

LOAD PurchaseYear,

Sales,

Flag_1,

Flag_2,

....

Flag_100

FROM .....

Now you will have a FlagType field containing Flag_1, Flag_2.... and a FlagValue field containing the flag value in addition to the normally loaded fields (year and sales). So your expression is then:

sum({<FlagValue={'1'}>} Sales)

And you can render the result in a crosstable easily with a pivot table (with FlagType as a dimension), should you so need.

HTH

Jonathan

Re: How to code Field name in Set expression Dynamically?

Fri, 14 Nov 2014 13:25:32 GMT

Hi Alessandro,

Thanks for the reply but 'Flag' has no link with my data and I don't think pivot table will help.

Is there any way to dynamically change the field name('Flag_') in set expression?

Regards,

Sagar Gupta

Re: How to code Field name in Set expression Dynamically?

Fri, 14 Nov 2014 13:26:40 GMT

Hi Jonathan,

Thanks for the reply but crosstable will not help in my case.

Is there any way to dynamically change the field name('Flag_') in set expression?

Regards,

Sagar Gupta

Re: How to code Field name in Set expression Dynamically?

jerrysvensson — Fri, 14 Nov 2014 14:04:37 GMT

Not sure I fully understand your need.

Use variables like : Set vOne = 1

sum({<Flag_$(vOne)={'1'}>}Sales)

Would that help?

Re: How to code Field name in Set expression Dynamically?

Sat, 15 Nov 2014 21:38:29 GMT

Hi Jerry,

Thanks for your reply

You correctly understood my problem. I am trying to do what you have mentioned but it's not working and I have to make it work somehow or else i have to write 400+expressions 😞

Also, I can't pass a variable(vOne) as it will always return a single value, instead i want to pass my dimension field

Let me know if you have some answer

Regards,

Sagar Gupta

Re: Re: How to code Field name in Set expression Dynamically?

Sun, 16 Nov 2014 05:18:07 GMT

Hi,

What Jerry Svensson suggested works perfectly.

The attached app will help, just select which flag you want from the list and the dynamic expression will get the results

Re: How to code Field name in Set expression Dynamically?

Sun, 16 Nov 2014 06:31:59 GMT

Hi,

Thanks for explaining it nicely

But in my case i don't want to make any selection. and i want to pass dimension field name instead of variable.

Thanks in advance

Regards,

Sagar Gupta