Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
mapratt82
Contributor

IF Then Where

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

Labels (3)
3 Replies
krishna_2644
Valued Contributor III

Re: IF Then Where

PL SHARE A MOCKED UP QVW.

mapratt82
Contributor

Re: IF Then Where

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.

MVP & Luminary
MVP & Luminary

Re: IF Then Where

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