Hi,
I am working with a dataset that has three columns for the same information. For example, there are three columns for tenant information; tenant1, tenant2, tenant3. If there is only one tenant, it should always be in tenant1 and the rest should be empty but that is not always the case. So if tenant 1 is empty and tenant 2 is not empty, I want to write what is in tenant 2 to tenant 1 and remove what is in tenant 2 column. And also do the same between tenant 2& 3 columns. Also if only Tenant 3 is filled I would like to write that info to tenant1.
For example, this is the table I have now.
Tenant1 | Tenant2 | Tenant3 |
Company A | ||
Company B | Company C | |
Company D |
And I would like the table to look like this after the ETL process:
Tenant1 | Tenant2 | Tenant3 |
Company A | ||
Company B | Company C | |
Company D |
Is there a way to do this? I can write the columns with the formula tool but I am not sure how to remove the original columns after I put the information in the first column. Thanks!
Does this work?
Insert a Record ID, then cleanse all of the data to remove blanks/nulls. Transpose the data by grouping on the RecorID. All other columns are Data columns. Filter out any of the blank/empty values. Generate a new row count with a multirow formula then transform and cross tab based on that new field.