IF Then Where

[mapratt82]

I'm probably overthinking this, but here is my problem. I need to have an IF statement in a group by Tax_ID that basically does the following:

If(any MTGBR > 0, use that record's AcctKey, 

if(any PymtBR > 0, use that record's AcctKey,  use the Mode(AcctKey) as PrimaryAddrKey

Group by Tax_ID

Please help! Thanks in advance!!!

[krishna_2644]

PL SHARE A MOCKED UP QVW.

[mapratt82]

I can't share a QVW but here is the load statement I have, of course I'm getting an "Invalid Expression" error on it.

Load
   Tax_ID_Number,
    if(Max(MTG)>0, "Address_ID",
      if(Max(Pymt)>0, "Address_ID", mode("Address_ID"))) as "Address_ID"
Resident Addrlink1
Group by Tax_ID_Number;

I don't want to add "Address_ID" to the group by because it doesn't give me the expected results. I need to have only one "Address_ID" associated with each unique "Tax_ID_Number". I need that "Address_ID" to be from the record that contains a value > 0 for "MTG", if one doesn't exist then check for a value > 0 for "Pymt" and use the "Address_ID" from that record, and if it doesn't exist use the mode("Address_ID") for that "Tax_ID_Number". I'm thinking maybe variables might present an answer, but I struggle with using variables.

[marcus_sommer]

Why not using an aggregation for all branches like:

Load
Tax_ID_Number,
if(Max(MTG)>0, mode("Address_ID"),
if(Max(Pymt)>0, mode("Address_ID"), mode("Address_ID"))) as "Address_ID"
Resident Addrlink1
Group by Tax_ID_Number;

respectively another function like min/max/avg or maybe a concat() and/or applying some kind of error-handling with something like: alt(mode("Address_ID"), avg("Address_ID"))

- Marcus