Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi guys. Here's my load script
NI,
Address1,
Address2,
Address3,
Address4,
Address5,
PostCode
From
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 |
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);
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);