topic Set Analysis with Field = Field in QlikView

Set Analysis with Field = Field

Thu, 09 Feb 2012 16:31:26 GMT

I am trying to wirte a set analysis statement for the sum where two fields have equal values.

I am using the syntax sum({<PrevWAC -= CurrWAC>} Balance) this returns the value equal to sum(Balance) so it is ignoring the set statement.

If I use an If statement of If(PrevWAC <> CurrWAC, sum(Balance), 0) in a straight table with the dimensions of 'account', 'PrevWAC', 'CurrWAC' the table shows the sum where PrevWAC is not equal to the CurrWAC at the account level and the total sum of rows gives me the correct answer.

I cannot figure out why the If statement works and the Set Analysis does not. I need to use Set Analysis because this is just one part of the statement I am trying to create. I have searched the posts and tried every solution idea I could find. Any Ideas?

Thank You

Patrick

Set Analysis with Field = Field

CELAMBARASAN — Thu, 09 Feb 2012 16:51:07 GMT

Hi,

you can try with this..

sum({<PrevWAC -= {CurrWAC}>} Balance)

Set Analysis with Field = Field

Anonymous — Thu, 09 Feb 2012 16:55:16 GMT

Patrick,

Your "set" is likely to include everything, if for each selection of PrevWAC there is value in CurrWAC which is not equal to PrevWAC.
Try this:
sum({-<PrevWAC = P(CurrWAC)>} Balance)

Regards,
Michael

Set Analysis with Field = Field

Thu, 09 Feb 2012 16:56:34 GMT

Thank you for the reply, but I did try that and it does not work.

I have also tried

sum({<PrevWAC = P(CurrWAC)>}Balance)

Set Analysis with Field = Field

Thu, 09 Feb 2012 17:00:47 GMT

Is PrevWAC and CurrWAC in the same table? If it is it might be able to create a flag in the script.

something like:

if (PrevWAC<>CurrWAC,1) as Flag

then in the table

sum({<Flag={'1'}>} Balance)

Set Analysis with Field = Field

Thu, 09 Feb 2012 17:06:58 GMT

I tried sum({-<PrevWAC = P(CurrWAC>}Balance) which gave me a new number but the not the one I am looking for.

Each 'account' has one value for 'PrevWAC' and 'CurrWAC' . I need the sum only for the accounts where PrevWAC and CurrWAC are the same.

Set Analysis with Field = Field

Thu, 09 Feb 2012 17:09:34 GMT

PrevWAC and CurrWAC are in the same table.

Set Analysis with Field = Field

Anonymous — Thu, 09 Feb 2012 17:37:30 GMT

Now it is not quite clear...

Could you please post an example of data in table format, with columns PrevWAC, CurrWAC, Balance, and the desired result?

Re: Set Analysis with Field = Field

Thu, 09 Feb 2012 19:14:45 GMT

To clarify there is an entry in the table for each month, for each account, that has a value for PrevWAC and CurrWAC. When PrevWAC and CurrWAC are different on an entry I want to sum those balances for each month. I have been including the month value in the set analysis, which works fine. I hope the attachment helps.

Re: Set Analysis with Field = Field

Thu, 09 Feb 2012 19:29:50 GMT

DId you try making a flag like I suggested?

LOAD "month_date",

MonthEnd("month_date") as EndOfMonth,

account,

CurrBal,

BalChg,

PrevWAC,

CurrWAC,

if(PrevWAC<>CurrWAC,1) as Flag;

SQL SELECT *

FROM SDM.dbo."cpi_roll_on_off"

where ProdGroup = 'ARM-4X1';

Then the Set analysis would just be:

sum({<Flag={'1'}>}CurrBal)

Re: Set Analysis with Field = Field

Thu, 09 Feb 2012 20:27:35 GMT

I tried the flag and it did work that way. Thank you. Curious on why it doesn't work the other way.

Set Analysis with Field = Field

Anonymous — Thu, 09 Feb 2012 21:34:39 GMT

It works for the same reason the "if" expression works. As for the "set" - I don't see an easy way here, and recommend the flag or "if".

Re: Set Analysis with Field = Field

Thu, 09 Feb 2012 21:54:57 GMT

I am using the flag and was able to integrate it into my file fine. Thank you all for your help.

Re: Set Analysis with Field = Field

hoangvvo — Wed, 22 Feb 2023 16:46:38 GMT

Here's the sample load:

tmp:NoConcatenate
load * inline

[_RecordID,fld1,fld2,balance
1,1,1,10
2,1,2,10
3,2,2,20
4,3,2,10
5,4,2,5
6,5,5,5
];

_RecordID is unique for each row in the table.

Here's the chat expression:

sum({$<[_RecordID]={"=fld1<>fld2"}>}balance)

Explanation: Return the _RecordID, when fld1 is not equal to fld2, then sum the balance.

This compare the two field and return _RecordID, which identify balance value on that row.

This works great as longa s _RecordID is unique, otherwise the return will find all _RecordID matching and sum its balance.

sum({$<UID= {"=PrevWAC <>CurrWAC"}>}Balance)

replace UID with your table's unique row id.