topic Re: Keeping address in one column in QlikView

Keeping address in one column

Wed, 23 Apr 2014 04:19:42 GMT

hi all,

Please find my attached data.xls.

in that i have data like this

but i need to combine all this in one column. i need to create a column called 'Address' and in that

the value should be

Address

100 SEMI STREET

SYDNEY

NSW 6000

LONDON

any idea to achieve this please share.

thanks

Re: Keeping address in one column

Wed, 23 Apr 2014 04:26:09 GMT

Hi,

Try with this script

LOAD NAME,

[INVESTOR INDICATOR],

TFN,

ABN,

TYPE,

ADD0,

ADD1,

ADD2,

ADD3,

ADD0 &' '& ADD1 &' '& ADD2 &' '& ADD3 as Address,

[INVESTMENT REF],

CCY,

DATE,

[GROSS AMT],

[TFN AMT],

[NON RES]

FROM

C:\Users\Desktop\DATA.txt

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

P.S. are you in Sydney?

Re: Keeping address in one column

trey_bayne — Wed, 23 Apr 2014 04:26:46 GMT

Try this:

Load

ADD0&' '&ADD1&' '&ADD2&' '&ADD3 as Address,

From DATA.txt;

The &' '& between the fields concatenates the values.

Re: Keeping address in one column

Wed, 23 Apr 2014 04:28:13 GMT

thnx for your kind reply,

thts fine but i need the address in down down not in one line

thnaks

Re: Keeping address in one column

Wed, 23 Apr 2014 04:30:20 GMT

use chr(10) in the concatenation.

like this

ADD0 & chr(10) & ADD1 & chr(10) & ADD2 & chr(10) & ADD3 as ADDRESS

Re: Keeping address in one column

Wed, 23 Apr 2014 04:32:12 GMT

If you are using it in a text box you can write something like this:

=ADD0 &'

' & ADD1 &'

'& ADD2 &'

'& ADD3

going in the above line after the &'

Re: Keeping address in one column

Wed, 23 Apr 2014 04:34:30 GMT

@Filippo, I am in Sydney today and tomorrow.

Re: Keeping address in one column

Wed, 23 Apr 2014 04:35:52 GMT

hi Ram & Filippo,

i need to achieve this in back end itself means in 'Edit Script'

any idea.

thanks:)

Re: Keeping address in one column

Wed, 23 Apr 2014 04:44:28 GMT

hi filippo,

is it possible to do it in straight table instead of text box.... i tried n straight table i didnt get it . any idea

thnx:)

Re: Keeping address in one column

Wed, 23 Apr 2014 04:51:53 GMT

It's a bit tricky, you can use the solution from ramagopr

and than inside the straight table you select in the preference->presentation->wrap cell text to be 4

Re: Keeping address in one column

Wed, 23 Apr 2014 04:58:00 GMT

thnk u very much this is what i want.

thnx again