Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

pzwonford
New Contributor II

Move fields over if blank

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

NIAddress1Address2Address3Address4Address5
123451 bob streetCheltenhamSouth

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

NIAddress1Address2Address3Address4Address5
123451 bob streetCheltenhamSouth
1 Solution

Accepted Solutions

Re: Move fields over if blank

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);

1 Reply

Re: Move fields over if blank

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);

Community Browser