Differentiate missing values

[Baron_askalot]

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!

[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!

[OllieClarke]

@Baron_askalot do you have an example .sav file that we could try?

[DiganP · Accepted solution]

@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?

[Baron_askalot]

Hi, I tried to upload an example .sav file but the upload function won't let me, even if I zipped it.

[Baron_askalot]

Ok, thanks for your evaluation and opinion. I will do it manually. As a backlog development topic, it might be nice to have a functionality that differentiates between nulls in so far, that the user can define own null values which can be easily activated or deactivated for certain analysis.

Best regards!

[DiganP · Accepted solution]

@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.