Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
mwscott1
Contributor

Concatenate Address With Blank Fields

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"

1 Solution

Accepted Solutions
vishsaggi
Esteemed Contributor III

Re: Concatenate Address With Blank Fields

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;

3 Replies
vishsaggi
Esteemed Contributor III

Re: Concatenate Address With Blank Fields

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

mwscott1
Contributor

Re: Concatenate Address With Blank Fields

This is an example of the data now

     

ADDRESS_LINE_1ADDRESS_LINE_2ADDRESS_LINE_3ADDRESS_LINE_4ADDRESS_LINE_5ZIP_POSTAL_CODE
3619 SUNSHINE AVENUE                                                                                                         HAPPY TOWN                  NE95842

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

vishsaggi
Esteemed Contributor III

Re: Concatenate Address With Blank Fields

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;