Remove zero values
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Is there a faster way to replace all the zero values in multiple columns with null/blank cell? I used filter function but there are more than 40 columns I need to do this for.
Example Input table
StudentID | Week 1 grade | Week 2 grade | Week 3 grade | Week 4 grade | Week 5 grade |
1 | 10 | 0 | 6 | ||
2 | 0 | 5 | 2 | ||
3 | 2 | 6 | 0 | 0 |
Output table
StudentID | Week 1 grade | Week 2 grade | Week 3 grade | Week 4 grade | Week 5 grade |
1 | 10 | 6 | |||
2 | 5 | 2 | |||
3 | 2 | 6 |
Thanks
Solved! Go to Solution.
- Labels:
- Topic of Interest
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @vivian123 , there is an easy way to do this using a transpose/crosstab method.
Transpose puts all your data into a single column, you can then use a formula on that column to replace zeros with nulls and finally crosstab back into the original format. I've added a Select tool in here to make sure the 'Value' field containing the main data is set to Integer rather than string.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@vivian123
We can also use a Multiple Column tool
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
hi @vivian123
For such data cleansing tasks, there are 2 considerations:
- WHERE: the rules to decide which columns should be cleansed
- WHAT: the rules to decide the modifications (change from WHAT to WHAT).
When you want to apply the same logic to all columns (e.g. rename column name, data cleansing etc...) or columns whose names/formats follow certain patterns (I.e. determining WHERE is simple and WHAT is the same for all WHERE), you should immediately think of the Multi-field tool.
When you want to apply different logic to different fields depending on both the field name and the field values (i.e. WHAT and WHERE are interdependent), the safer choice is to do the following steps:
1) add unique ID to record (so that you can match the original results later)
2) Transpose so that you have the field name and field values in the data for further processing.
3) Apply the rules to get the new values (typically with FORMULA tool)
4) Crosstab to get back the original data format.
Cheers,
Dawn.