topic Remove blank fields in QlikView

Remove blank fields

danielnevitt — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Remove blank fields

Or — Tue, 28 Aug 2018 10:05:17 GMT

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).

Re: Remove blank fields

OmarBenSalem — Tue, 28 Aug 2018 10:25:47 GMT

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:

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: