topic Compare with current selection and not per row in QlikView

Compare with current selection and not per row

Sun, 29 Jun 2014 21:33:03 GMT

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' );

Re: Compare with current selection and not per row

swuehl — Sun, 29 Jun 2014 22:44:00 GMT

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))

Re: Compare with current selection and not per row

Sun, 29 Jun 2014 22:56:57 GMT

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

Re: Compare with current selection and not per row

swuehl — Mon, 30 Jun 2014 18:55:39 GMT

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).

Re: Compare with current selection and not per row

Tue, 01 Jul 2014 07:25:51 GMT

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.