Move fields over if blank

[Anonymous]

Hi guys. Here's my load script

NI,

Address1,

Address2,

Address3,

Address4,

Address5,

PostCode

From (ooxml, embedded labels, Table is 'Report');

Some of the addresses have spaces. For example

NI	Address1	Address2	Address3	Address4	Address5
12345	1 bob street		Cheltenham		South

How can i run the load script so that these are all side by side and move over if the field is null? I need to avoid having spaces in the data

NI	Address1	Address2	Address3	Address4	Address5
12345	1 bob street	Cheltenham	South

[sunny_talwar · Accepted solution]

May be this:

Table:

LOAD NI,

     SubField(If(Len(Trim(Address1)) > 0, Trim(Address1)&'|')&

       If(Len(Trim(Address2)) > 0, Trim(Address2)&'|')&

       If(Len(Trim(Address3)) > 0, Trim(Address3)&'|')&

       If(Len(Trim(Address4)) > 0, Trim(Address4)&'|')&

       If(Len(Trim(Address5)) > 0, Trim(Address5)), '|', 1) as Address1,

     SubField(If(Len(Trim(Address1)) > 0, Trim(Address1)&'|')&

       If(Len(Trim(Address2)) > 0, Trim(Address2)&'|')&

       If(Len(Trim(Address3)) > 0, Trim(Address3)&'|')&

       If(Len(Trim(Address4)) > 0, Trim(Address4)&'|')&

       If(Len(Trim(Address5)) > 0, Trim(Address5)), '|', 2) as Address2,

     SubField(If(Len(Trim(Address1)) > 0, Trim(Address1)&'|')&

       If(Len(Trim(Address2)) > 0, Trim(Address2)&'|')&

       If(Len(Trim(Address3)) > 0, Trim(Address3)&'|')&

       If(Len(Trim(Address4)) > 0, Trim(Address4)&'|')&

       If(Len(Trim(Address5)) > 0, Trim(Address5)), '|', 3) as Address3,

     SubField(If(Len(Trim(Address1)) > 0, Trim(Address1)&'|')&

       If(Len(Trim(Address2)) > 0, Trim(Address2)&'|')&

       If(Len(Trim(Address3)) > 0, Trim(Address3)&'|')&

       If(Len(Trim(Address4)) > 0, Trim(Address4)&'|')&

       If(Len(Trim(Address5)) > 0, Trim(Address5)), '|', 4) as Address4,

     SubField(If(Len(Trim(Address1)) > 0, Trim(Address1)&'|')&

       If(Len(Trim(Address2)) > 0, Trim(Address2)&'|')&

       If(Len(Trim(Address3)) > 0, Trim(Address3)&'|')&

       If(Len(Trim(Address4)) > 0, Trim(Address4)&'|')&

       If(Len(Trim(Address5)) > 0, Trim(Address5)), '|', 5) as Address5

FROM

[https://community.qlik.com/thread/213890]

(html, codepage is 1252, embedded labels, table is @1);