Row comparison

[sinanozdemir]

Hi all,

I have the below table:

Company	Amount
A	100
B	200
C	90
D	85

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

Company	Amount	Comparison
A	100	No Comparison Necessary
B	200	B is greater than A
C	90	C is lower than A
D	85	D 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.

[sunny_talwar]

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;

[sunny_talwar]

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

[Gabriel]

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

[avinashelite]

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

[sunny_talwar · Accepted solution]

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

[sinanozdemir]

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.

[sunny_talwar]

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]

Thank you again.

[sunny_talwar]

Not a problem

I am glad I proved helpful.