topic Change variable dynamically (variable variable) in QlikView

Change variable dynamically (variable variable)

Fri, 02 Sep 2011 14:22:30 GMT

Hi all,

is it possible to do something like this in a formula field of an object?...:

$(myVar) = 99;

if( $(myVar) = 99, 'okay', 'not okay' )

- another example (textbox-formula) -

$(myVar) = 99;

=$(myVar)

thanks,

jup

Change variable dynamically (variable variable)

martin59 — Fri, 02 Sep 2011 15:15:54 GMT

Hi,

I don't understand what you want.

Could you precise Your need ?

Martin

Change variable dynamically (variable variable)

its_anandrjs — Fri, 02 Sep 2011 15:20:03 GMT

Hi,

Please clear your requirements what you actually wants if you want to do calculation in variable if so it waorks.

Regards,

Anand

Change variable dynamically (variable variable)

Mon, 05 Sep 2011 12:08:05 GMT

Okay, sorry! Let me try to explain what I mean. I just want to minimize script-code using custom variables ...

I have 2 items in the database with a different value of [HEL_PLACEMENT_Platzierungsgröße]:

1.) '728 x 90'

2.) '120 x 600'

I want to display the sum([HEL_DATA_Imps mit Defaults]) of the items which has the most impressions.

If '728 x 90' has more IMPRESSIONS than '120 x 600' then display the sum() of '728 x 90'-items else display the sum() of the '120 x 600'-items.

I already do this with that formula:

This works great. ... Back to my question... ...

Is it possible to minimize the formula's code? I'm thinking about something like this:

First I want to fill 2 custom variables with the sum()-Result. After that I compare the 2 VARIABLES and display the result. Any idea how to do this?

By the way just another quick question:

Is there a possibility to user a different boolean-function instead of "if". I don't want to do somethng like this (switch / case):

Can I do something like that:

thanks,

jup

Change variable dynamically (variable variable)

Mon, 05 Sep 2011 12:44:34 GMT

Hi,

I'd recommend looking up "Script Variables" (you want to use Set rather than Let for dynamic variables) and "Dual" in the QV help. Script Variables allow you to define dynamic variables that are evaluated when called in an expression, and the Dual() function will allow you to associate month numbers and names. You could also call the Month() function on an entire timestamp to just return the month's name.

Regards,

Michael

Change variable dynamically (variable variable)

Mon, 05 Sep 2011 15:36:45 GMT

Hi,

could you post an example for "let",please?

I placed a textbox on my form and typed the following code in the formula-field:

-----------------------------------------------------------------------------------------------

=LET testvar=1

=testvar

-----------------------------------------------------------------------------------------------

I expect that the value "1" will be displayed in the textbox, but "-" appears.

could you help me? I only want to define a variable first and display the variable's value after that (in ONE formula on an object).

thanks,

jup

Change variable dynamically (variable variable)

Mon, 05 Sep 2011 15:47:08 GMT

Hi,

Script variables must be defined in the load script (ctrl+e), but are then evaluated each time they are called (if defined using set).

Regards,

Michael

Re: Change variable dynamically (variable variable)

Tue, 06 Sep 2011 15:30:01 GMT

hmmm. not really happy with that 😕

maybe someone could help me with the attached (simple) example.

this is my data (will be loaded inline in the qvw file):

I have 3 different kinds of "AdTypes":

-sb

-sky

-mrec

Now I want to display (on monthly base) the summarized impressions for only the AdType, which has the most impressions in this month.- In this example it would be

1.) Jan (1): 3 Impressions (maxSUM="sb")

2.) Feb (2): 3 Impressions (maxSUM="mrec")

3.) Mar (3): 3 Impression (maxSUM="sky")

I want to use a table for this operation with the dimension "Month" and a formula.

Now I need you... could you post me a (simple/easy) formula to do the operation explained above?

I already did it with a formula that covers only "sb" and "sky" at the moment:

The formula is working, but it looks ugly. And it will look more ugly, if I include the "mrec". (Note that there is not only "mrec" to include in the formula - there are many other AdTypes... :-/)

Result at the moment (only "sb" + "sky" covered):

Any idea to get a simply, easy formula (maybe with variables in the code)?

Please find attached my example.

thanks,

jup

Re: Change variable dynamically (variable variable)

Tue, 06 Sep 2011 17:31:36 GMT

If I understand you correctly, please see if this works:

=if(
    isnull(
        FirstSortedValue(
            AdType,
            -Impressions
        )
    ) = -1,
    'Multiple ad types',
    FirstSortedValue(
        AdType,
        -Impressions
    )
)
&
': '
&
max(
    Impressions
)

Re: Change variable dynamically (variable variable)

Wed, 07 Sep 2011 09:56:42 GMT

Very very interesting answer, but not the result I need. Your formula is based on the "sort"-function and display the highest sorted value...

Your formula will get this result:

But I need:

Month	Impressions
1	sb: 3
2	mrec: 3
3	sky: 3

I need to calculate the ADTYPE with the highest SUM(impressions) each MONTH - not top-sorted(impressions). - you know?

regards,

jup

Re: Change variable dynamically (variable variable)

Wed, 07 Sep 2011 11:55:27 GMT

hi,

instead of if() staments u can use match(), wildmatch() functions...

vivek