Compare with current selection and not per row

[#1]

I've this problem and i'am with this within days and can't figure out a solution.

I've a RaiserPlantId wich i made a selection from a listbox, and i i've a the same raiserPlantId in the data table now i want to compare if the field 'Responsible' is equal to the Selected raiserPlantId and not the raiserPlantId in the table data and sum.

What i've done:

=sum(If(Responsible = RaiserPlantID, (Auth*XRATE2USD),0)) -> compares per row

i also have tried this but it returns null

=sum(If(Responsible = GetFieldSelections(RaiserPlantID), (Auth*XRATE2USD),0)) - returns null

For better understanding my problem, here is a scr:

I appreciate if someone help me with this problem is driving me crazy.

In the listbox the dimension is RaiserPlantId

I don't know if it will help but here it's my load:

DATA:

LOAD Year,

     Week,

     RaisedDate,

     id,

     UPPER(TRIM(RaisedBy)) as RaisedBy,

     UPPER(TRIM(sFrom)) as sFrom,

     sFromName,

     UPPER(TRIM(sTo)) as sTo,

     sToName,

     MOD,

     UPPER(TRIM(REA)) as REA,

     text(UPPER(TRIM(Responsible))) as Responsible,

     Auth,

     Val as Currency,

     ST,

     Category,

     Reason,

     ProgramName,

     UPPER(TRIM(RaiserPlant)) as RaiserPlantID,

     text(UPPER(TRIM(Haulier))) as Haulier

FROM

(txt, utf8, embedded labels, delimiter is ',', msq)

WHERE

  (UPPER(TRIM(ST)) <> 'Z') and

  (

    UPPER(RaiserPlant) = '351E'  OR

    UPPER(RaiserPlant) = '179A'  OR

    UPPER(RaiserPlant) = '179B'  OR

    UPPER(RaiserPlant) = '1215'  OR

    UPPER(RaiserPlant) = 'ALTAA' OR

    UPPER(RaiserPlant) = '295A'  OR

    UPPER(RaiserPlant) = '604E'  OR

    UPPER(RaiserPlant) = '6605'  OR

    UPPER(RaiserPlant) = '710A'  OR

    UPPER(RaiserPlant) = '710B'  OR

    UPPER(RaiserPlant) = '6865'  OR

    UPPER(RaiserPlant) = '1225' );

[swuehl · Accepted solution]

I am not really sure what you want to achieve.

GetFieldSelections will return a string containing your selections which you can't just compare to the Field value of Responsible.

Maybe like this

=sum( If( Match( Responsible, $(=chr(39) & concat( RaiserPlantID,  chr(39) & ', ' & chr(39) ) & chr(39) ) ), (Auth*XRATE2USD),0))

[#3]

I've tried your expression and the values seems alright, but before i put as it correct answer tomorrow morning i will check if everything is alright, what i'am trying to achieve is:

If( Responsible = Selected Raiser plant, is plant responsibility)

if( responsible <> Selected RaiserPlant , is others Responsibility)

Can you make a brief explanation what you are doing ? In detail the match function and the char(39)?

thanks for the help you save me

[swuehl]

Fabio,

$(=chr(39) & concat( RaiserPlantID,  chr(39) & ', ' & chr(39) ) & chr(39) )

is a so called dollar-sign-expansion, an expansion that evaluated the expression contained to text and expands it in the embedding expression before latter is evaluated (like a macro expansion in programming).

See also

http://community.qlik.com/blogs/qlikviewdesignblog/2013/11/18/dollar-expansions

The Match() function compares the first argument with each of the other argument values, and returns the position of the matching second argument or zero when no matching value found.

chr(39) will create single quote characters and concatenate these with the strings ouput by concat() function.

My construct with the dollar sign expansion builds these arguments from your selection dynamically, e.g. after dollar sign expansion, you'll find:

=sum(if (Match( Responsible, '179A', '179B'), (Auth*XRATE2USD) ,0))

[#5]

It works like a charm

In the past i already tried something similar with wildmatch, match and concat but i was struggling with the quotes, I've tried something like wildmatch(Responsible, & '*' & RaiserPlantID & '*') but doesnt work.

Thanks for your help and the explanation.