Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

sinanozdemir
Valued Contributor III

Row comparison

Hi all,

I have the below table:

CompanyAmount
A100
B200
C90
D85

What I want to do is to compare A to other companies:

CompanyAmountComparison
A100No Comparison Necessary
B200B is greater than A
C90C is lower than A
D85D is lower than A

I just wanted to see if there is an easy way to do this with or without creating additional dimensions/tables in the load script.

Straight or pivot table solutions are highly desirable.

Thank you.

1 Solution

Accepted Solutions

Re: Row comparison

Front end solution:

Dimension:

Company

Amount

Expression:

=If(Company = 'A', 'No Comparison Necessary',

If(Only(TOTAL {<Company = {A}>}Amount) > Amount, Company & ' is greater than A',

If(Only(TOTAL {<Company = {A}>}Amount) < Amount, Company & ' is less than A')))


Capture.PNG

13 Replies

Re: Row comparison

This may be not what you want, because I am sure the data you provided above must be a sample, but this might give you an idea to proceed.

Table:

LOAD Company,

    Amount

FROM

[https://community.qlik.com/thread/172514]

(html, codepage is 1252, embedded labels, table is @1);

Temp:

LOAD FirstValue(Amount) as Temp

Resident Table;

LET vTemp = Peek('Temp');

Join (Table)

LOAD Company,

  If(Company = 'A', 'No Comparison Necessary',

  If(Amount > $(vTemp), Company & ' is greater than A',

  If(Amount < $(vTemp), Company & ' is lower than A'))) as Comparison

Resident Table;

DROP Table Temp;

Re: Row comparison

Just saw your comment that you need this on the front end.

Partner
Partner

Re: Row comparison

Hi,

You can you PEEK() function in the script to compare fields.

Peek(fieldname [, row [, tablename ]] )

i.e. IF(Amount= Peek('Amount'), 1) AS %FieldCompareFlag

You can use this flag in your straight table and hide it

Hope this helps

Re: Row comparison

in the front end you can try like this:

if(Company='A','No Change', if(Amount<=$(vAmount),Company&' is Lower than A', Company&' is Greater than A'))

NOTE: create variable vAmount=num(only({<Company={'A'}>}Amount))

Re: Row comparison

Front end solution:

Dimension:

Company

Amount

Expression:

=If(Company = 'A', 'No Comparison Necessary',

If(Only(TOTAL {<Company = {A}>}Amount) > Amount, Company & ' is greater than A',

If(Only(TOTAL {<Company = {A}>}Amount) < Amount, Company & ' is less than A')))


Capture.PNG

sinanozdemir
Valued Contributor III

Re: Row comparison

You are the man, Sunny.

Thank you so much.

I may have a follow up question later on though.

I know Robert Mika posted a solution and I don't know what happened to it. It disappeared.

Thank you again.

Re: Row comparison

Not sure either, but if you need to see his response, this was it:

if(rowNO()=1,'no comaprision',if(rangesum(above(sum(Amount),1,1))<SUM(Amount),'lower than A','Greater than A'))

sinanozdemir
Valued Contributor III

Re: Row comparison

Thank you again.

Re: Row comparison

Not a problem

I am glad I proved helpful.