Remove blank fields

[danielnevitt]

Hi,

I would appreciate some help with removing blank fields from a concatenated address.

I have the following fields:

addressline1

addressline2

addressline3

addressline4

The four fields are not always all populated, so I would like a way to remove them if blank.  Is this possible?

Example:

addressline1        addressline2           addressline3          addressline4

100                      Road A                    Town A                  City A

200                      Road B                                                  City B

I would like the above to show as:

100 Road A, Town A, City A

200 Road B, City B

Thanks,

Daniel

[Or]

If you concatenate the string, the blanks won't actually do anything. Concatenating a blank to a string doesn't change the string in any way. I can only see a potential problem with the commas you are appending, and I'm not sure how you're doing that, so I can't suggest a quick fix. That said, you can just keep things as-are and replace(Address,',,',',') on the concatenated result (replace any instance of two commas with one comma).

[OmarBenSalem]

Load *, left(if(len(addressline1)=0,'',addressline1&', ')&

if(len(addressline2)=0,'',addressline2&', ')&

if(len(addressline3)=0,'',addressline3&', ')&

if(len(addressline4)=0,'',addressline4&', ') ,Len(if(len(addressline1)=0,'',addressline1&', ')&

if(len(addressline2)=0,'',addressline2&', ')&

if(len(addressline3)=0,'',addressline3&', ')&

if(len(addressline4)=0,'',addressline4&', '))-2) as AddressV1 Inline [

addressline1  ,      addressline2 ,          addressline3 ,         addressline4

100   ,                   Road A ,                   Town A     ,            City A

200  ,                    Road B      ,                           ,                 City B

200  ,                    ,                                  ,          City B

];

Result:

or

Load *, replace(replace(replace(addressline1&', '&addressline2&', '&addressline3&', '&addressline4,', ,',','),', ,',','),', ,',',') as AddressV2 Inline [

addressline1  ,      addressline2 ,          addressline3 ,         addressline4

100   ,                   Road A ,                   Town A     ,            City A

200  ,                    Road B      ,                           ,                 City B

200  ,                    ,                                  ,          City B

];

Result: