topic Re: Row comparison in QlikView

Row comparison

sinanozdemir — Tue, 14 Jul 2015 13:49:31 GMT

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.

Re: Row comparison

sunny_talwar — Tue, 14 Jul 2015 13:55:51 GMT

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

sunny_talwar — Tue, 14 Jul 2015 13:57:01 GMT

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

Re: Row comparison

Gabriel — Tue, 14 Jul 2015 13:58:37 GMT

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

avinashelite — Tue, 14 Jul 2015 14:04:56 GMT

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

sunny_talwar — Tue, 14 Jul 2015 14:48:18 GMT

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

Re: Row comparison

sinanozdemir — Tue, 14 Jul 2015 15:15:52 GMT

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

sunny_talwar — Tue, 14 Jul 2015 15:18:32 GMT

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

Re: Row comparison

sinanozdemir — Tue, 14 Jul 2015 15:21:02 GMT

Thank you again.

Re: Row comparison

sunny_talwar — Tue, 14 Jul 2015 15:23:53 GMT

Not a problem

I am glad I proved helpful.

Re: Row comparison

sinanozdemir — Wed, 15 Jul 2015 23:10:08 GMT

Hi Sunny,

I also came up with a solution which is a bit different than yours, but would you mind looking at the attached document and my expressions?

It is simply comparing every company to company A within regions:

And here is the expression for folks who cannot open qvw files:

As far as I know, this expression works because if statement is used within a function so it makes it row level. I might be wrong. However, do you think this could be a solution as well?

Thank you again.

Re: Row comparison

sunny_talwar — Thu, 16 Jul 2015 02:59:36 GMT

Hey Sinan -

Your method definitely works and it works perfectly it seems. But, I am still a newbie to be able to tell which method is better. I have another option for the new data you shared. This one doesn't use Aggregate. Personally, I try to minimize the use of aggregate function. With that said, I am not really sure if my expression with TOTAL is any better than the one you have, but since you asked for my suggestion, I am going to give you another expression and you can probably stress test them to see which one is a better option.

Existing Expression:

=If(RowNo() = 1, 'No Comparison',

If(Sum(Aggr(if(Company <> 'A',Amount), Region, Company)) >

Above(Sum(if(Company = 'A', Amount)),1,1), 'Greater than A', 'Lower than A'))

New Expression:

=If(Match(Company, 'A'), 'No Comparison',

If(Sum(Total <Region> {<Company = {'A'}>}Amount) > Sum(Amount), 'Lower than A', 'Greater than A'))

Also attaching the qvw document for you to review.

HTH

Best,

Sunny

Re: Row comparison

sinanozdemir — Thu, 16 Jul 2015 03:03:00 GMT

Thank you so much Sunny for your time.

Yours looks much easier to understand and read.

Thanks a lot again.

Re: Row comparison

sunny_talwar — Thu, 16 Jul 2015 03:04:33 GMT

No problem at all

I am glad together we can get the best solution.