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;
