Data Manipulation

EvansM

Community,

I need help to create 3 columns ([STREET],[ADDR1],[ADDR2],[COADDR]) according with the scenarios below. Refer the spreadsheet attached.

1. Addr_Care_of is not null --place in COADDR, place Addr_1 in STREET,  Addr_2 in ADDR2
2. Addr_1 and Addr_2 are not null and Addr_3 is null -- place Addr_1 in STREET, Addr_2 in ADDR2
3. Addr_1 and Addr_2 are both null and Addr_3 is not null -- place Addr_3 in STREET.
4. Addr_1 is not null, Addr_2 is null, Addr_3 is not null -- place Addr_1 in STREET, place Addr_3 in COADDR
5. Addr_1 is null, Addr_2 is not null, Addr_3 is null – place Addr_2 in STREET
6. Addr_1 is not null, Addr-2 is not null, Addr_3 is not null -- Addr_1 in COADDR, place Addr_2 in STREET, place Addr_3 in ADDR2 (we will print address exactly like it is in their files as these are scrambled)
7. NOTE: If all 4 fields are populated. Please see attached image for output

                       

Thanks in advance

IDMaster.xlsx

fmvizcaino

Hi @EvansM ,

I have created one filter tool for each rule to help you control your clauses more easily and to also check for any row that isn't include in any of the rules as we can see happening with the 3rd row.

Let me know if that works for you.

Best,

Fernando Vizcaino

2019-12-11 Data Manipulation.yxmd