Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
mapratt82
Creator
Creator

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 (2)
3 Replies
krishna_2644
Specialist III
Specialist III

PL SHARE A MOCKED UP QVW.

mapratt82
Creator
Creator
Author

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
MVP & Luminary
MVP & Luminary

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