This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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.
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)