Differentiate missing values

Dear all,

is there a way to differentiate missing values when importing data into the alteryx designer? I want to import an SPSS file (.sav) in which for some variables no entries exist (a cell is empty and not coded) and some missing values exist that are already defined (a cell is coded but not eligable for further calculations, e.g. answers like "I dont know" or "no specification". My problem is that when I just import the sav-file all the cells described are treated as "nulls" and I can no longer differentiate between them.

Thank you very much for your help!

Kind Regards!

Workflow

Accepted answers

DiganP

@Baron_askalot I believe you would have to recode the functions manually inside of Alteryx. I don't think the logic will carry over inside of Alteryx in the .sav file. It shouldn't be that complicated to do so. You can use the formula tool to write functions or conditional statements accounting for values 8 or 9.

What logic would you like to incorporate?

DiganP

@Baron_askalot You can differentiate values once you bring in the data! You can even turn existing data into nulls in the formula tool once the data has been read in. Alteryx just splits the process into 2 (one of reading in data and one for defining nulls). This gives user more flexibility when working with data. Hope this helps.

All comments

DiganP

@Baron_askalot You can use the filter tool to filter out any of the records or nulls you might not need to be processed later. You can also use the data cleansing tool to replace the nulls with 0's and empty records with a blank. How does your dataset look like? Can you provide a sample of what you are trying to do?

Baron_askalot

Hi Digan, thanks for your reply. I know I could use these tools after importing the data, but all the coding I need is already contained in the .sav file I want to import. And since I have more than 500 variables using occasionally different codes for missing values I dont want to do the work all over again. In some cases of my analysis I need to consider missing values (the coded ones like "I dont know"-answers) and in some I dont.. thats why I cannot just mark them all as nulls and have them mixed up with empty cells ("real nulls").

Attached you find an example: Variable 1 can have the values 1, 2, 8, 9 or blank. SPSS treats blanks as sysmis and I additionaly defined value 8 and 9 as missing values (e.g. I dont know answers). When importing the data in alteryx, the values 8, 9 and blanks are all treated as nulls.

So is there a chance when importing the .sav file to have alteryx diffentiate between those cases or do I have to recode is manually, using the tools you already mentioned?

Thank you very much!

example.xlsx

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703