Can you solve a nested IF mystery?

alexis — Thu, 01 Nov 2012 12:27:32 GMT

Hi all,

In the following code I have a nested IF statement.
As you can see I have 2 variants one commented out and one not
The commented out one checks for COUNT(Distinct....) starting from the lowest number going up
The (un)commented version checks for COUNT(Distinct....) starting from a high number going down
Just for the record my dataset comprises records that are either '1 Acquirer' or '2 Acquirers' and nothing less or more so the IF=3 and 4 or more are just for illustration
One of these nested IF statements gives me correct results and the other (the commented out version) does not.

Any ideas why?

Acquirers:
Load TID,
//
//If((Count(DISTINCT Acquirer))=1,'1 Acquirer',
//      If((Count(DISTINCT Acquirer))=2,'2 Acquirers',
//             If((Count(DISTINCT Acquirer))=3,'3 Acquirers',
//                    If((Count(DISTINCT Acquirer))=4,'4 Acquirers','Over 4 Acquirers')
//                    )
//             )
//      )                                        as #Acquirers
If((Count(DISTINCT Acquirer))=4,'4 Acquirer',
      If((Count(DISTINCT Acquirer))=3,'3 Acquirers',
             If((Count(DISTINCT Acquirer))=2,'2 Acquirers',
                    If((Count(DISTINCT Acquirer))=1,'1 Acquirers','No Acquirers')
                    )
             )
      )                                        as #Acquirers
      
Resident HiPos
group by TID;
JOIN (HiPos)
LOAD TID,
          #Acquirers As NoOfAcquirers
RESIDENT Acquirers;
drop table Acquirers;

Re: Can you solve a nested IF mystery?

swuehl — Thu, 01 Nov 2012 14:24:27 GMT

I can't see any differences in the results between these two expressions except for the default path 'Over 4' vs 'no'.

See attached.

Probably my simple setting is not fully equivalent to yours. So please post a small sample (or the creating script snippet) that demonstrates your issue.

Regards,

Stefan

topic Can you solve a nested IF mystery? in QlikView

Can you solve a nested IF mystery?

Re: Can you solve a nested IF mystery?