topic Re: Concatenate Address With Blank Fields in QlikView

Concatenate Address With Blank Fields

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

I am trying to concatenate several address fields from one table into one formatted address. If the field has a NULL value I would like to skip it and go to the next. I have tried + and & nothing seems to work.

SQL SELECT

"ADDRESS_LINE_1",

"ADDRESS_LINE_2",

"ADDRESS_LINE_3",

"ADDRESS_LINE_4",

"ADDRESS_LINE_5",

"ZIP_POSTAL_CODE"

FROM DBZA.APR;

If there is a way to insert line breaks to somewhat format the address like below.

ADDRESS_LINE_1

ADDRESS_LINE_2

ADDRESS_LINE_3

ADDRESS_LINE_4

ADDRESS_LINE_5 + ZIP_POSTAL_CODE"

Re: Concatenate Address With Blank Fields

vishsaggi — Fri, 23 Jun 2017 20:04:30 GMT

Can you elaborate your question a little please with some expected output you want to see?

Re: Concatenate Address With Blank Fields

mwscott1 — Fri, 23 Jun 2017 20:22:05 GMT

This is an example of the data now

ADDRESS_LINE_1	ADDRESS_LINE_2	ADDRESS_LINE_3	ADDRESS_LINE_4	ADDRESS_LINE_5	ZIP_POSTAL_CODE
3619 SUNSHINE AVENUE				HAPPY TOWN NE	95842

I would all the address info concatenated as a new field ADDRESS and with the NULL values removed.

3619 SUNSHINE AVENUE HAPPY TOWN NE 95842

or possible formatted with line breaks like this

3619 SUNSHINE AVENUE

HAPPY TOWN NE 95842

Re: Concatenate Address With Blank Fields

vishsaggi — Fri, 23 Jun 2017 20:38:27 GMT

Try this may be:

LOAD *,

"ADDRESS_LINE_1" & ' ' & "ADDRESS_LINE_2" & ' ' & "ADDRESS_LINE_3" & ' ' & "ADDRESS_LINE_4" & ' ' &

"ADDRESS_LINE_5" & ' ' & "ZIP_POSTAL_CODE" AS [FUll Adress];

SQL SELECT

"ADDRESS_LINE_1",

"ADDRESS_LINE_2",

"ADDRESS_LINE_3",

"ADDRESS_LINE_4",

"ADDRESS_LINE_5",

"ZIP_POSTAL_CODE"

FROM DBZA.APR;