1 Reply Latest reply: Apr 20, 2016 9:34 AM by Sunny Talwar RSS

    Move fields over if blank

    Paul Wonford

      Hi guys. Here's my load script

       

      NI,

      Address1,

      Address2,

      Address3,

      Address4,

      Address5,

      PostCode

       

      From [c:\Test.xlsx] (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
        • Re: Move fields over if blank
          Sunny Talwar

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