Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
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

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