2 Replies Latest reply on Sep 7, 2016 6:36 PM by Sherzodbek Ibragimov

# Combining two dimensions to get normalized data

I have two dimensions: level of study (Undergraduate, Graduate, Null [Unknown as alias]) and student type (Degree seeking, Non degree seeking, Transfer, International, First Time Freshman, First-time Freshman, First Time Graduate Student, Graduate Transfer, OLD, Null [Unknown as alias]). The problem is both of dimensions are incomplete and inconsistent, thus i can't use only one of the to get normalized 'level of study'. So, I am trying to to use both dimensions so I can come up with more accurate level of study with less unknows. Well, I have tried following formula, but doesn't seem to work. I should be have about 23k real unknowns per excel, but my result shows way more than that. Could you please direct me on that? I really appreciate that.

IF [Student Type]='Degree Seeking' THEN 'Undergraduate'
ELSEIF ([Level of Study]= 'Undergraduate' and [Student Type]='First Time Freshman') THEN 'Undergraduate'
ELSEIF ([Level of Study]='Unknown' and [Student Type]='First Time Freshman') THEN 'Undergraduate'
ELSEIF [Student Type]='First-time Freshman' THEN 'Undergraduate'
ELSEIF ([Level of Study]='Unknown' and [Student Type]='International') THEN 'Undergraduate'
ELSEIF [Student Type]='Non degree seeking' THEN 'Undergraduate'
ELSEIF ([Level of Study]='Unknown' and [Student Type]='Transfer') THEN 'Undergraduate'
ELSE 'Unknown' END

• ###### 1. Re: Combining two dimensions to get normalized data

No chance for data or a glimpse into values in both columns?

My guess is that [Student Type]=NULL should be replace with ISNULL([Student Type])

Also, for readability, I'd play with either CASE or nested IF.

1 of 1 people found this helpful
• ###### 2. Re: Combining two dimensions to get normalized data

Hi Dmitry,

sorry for a late reply. After I have posted it, I also asked Jonathan Drummey for a help. You are right. Unknown/Empty is not a value, thus ISNULL should be used to solve the problem. Thanks for your reply.