topic Move fields over if blank in QlikView

topic Move fields over if blank in QlikView https://community.qlik.com/t5/QlikView/Move-fields-over-if-blank/m-p/1068069#M638803 <HTML><HEAD></HEAD><BODY><P>Hi guys. Here's my load script</P><P></P><P>NI,</P><P>Address1,</P><P>Address2,</P><P>Address3,</P><P>Address4,</P><P>Address5,</P><P>PostCode</P><P></P><P>From <C> (ooxml, embedded labels, Table is 'Report');</C></P><P></P><P>Some of the addresses have spaces. For example</P><P></P><TABLE border="1" class="jiveBorder" style="border: 1px solid rgb(0, 0, 0); width: 100%;"><TBODY><TR><TH style="text-align: center; background-color: #6690bc; color: #ffffff; padding: 2px;" valign="middle">NI</TH><TH style="text-align: center; background-color: #6690bc; color: #ffffff; padding: 2px;" valign="middle"><STRONG>Address1</STRONG></TH><TH style="text-align: center; background-color: #6690bc; color: #ffffff; padding: 2px;" valign="middle"><STRONG><STRONG>Address2</STRONG></STRONG></TH><TH style="text-align: center; background-color: #6690bc; color: #ffffff; padding: 2px;" valign="middle"><STRONG><STRONG>Address3</STRONG></STRONG></TH><TH style="text-align: center; background-color: #6690bc; color: #ffffff; padding: 2px;" valign="middle"><STRONG><STRONG>Address4</STRONG></STRONG></TH><TH style="text-align: center; background-color: #6690bc; color: #ffffff; padding: 2px;" valign="middle"><STRONG><STRONG>Address5</STRONG></STRONG></TH></TR><TR><TD style="padding: 2px;">12345</TD><TD style="padding: 2px;">1 bob street</TD><TD style="padding: 2px;"></TD><TD style="padding: 2px;">Cheltenham</TD><TD style="padding: 2px;"></TD><TD style="padding: 2px;">South</TD></TR></TBODY></TABLE><P></P><P>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</P><P></P><TABLE border="1" class="jiveBorder" style="border: 1px solid #000000; width: 100%;"><TBODY><TR><TH style="text-align: center; background-color: #6690bc; color: #ffffff; padding: 2px;" valign="middle">NI</TH><TH style="text-align: center; background-color: #6690bc; color: #ffffff; padding: 2px;" valign="middle"><STRONG>Address1</STRONG></TH><TH style="text-align: center; background-color: #6690bc; color: #ffffff; padding: 2px;" valign="middle"><STRONG><STRONG>Address2</STRONG></STRONG></TH><TH style="text-align: center; background-color: #6690bc; color: #ffffff; padding: 2px;" valign="middle"><STRONG><STRONG>Address3</STRONG></STRONG></TH><TH style="text-align: center; background-color: #6690bc; color: #ffffff; padding: 2px;" valign="middle"><STRONG><STRONG>Address4</STRONG></STRONG></TH><TH style="text-align: center; background-color: #6690bc; color: #ffffff; padding: 2px;" valign="middle"><STRONG><STRONG>Address5</STRONG></STRONG></TH></TR><TR><TD style="padding: 2px;">12345</TD><TD style="padding: 2px;">1 bob street</TD><TD style="padding: 2px;">Cheltenham</TD><TD style="padding: 2px;">South</TD><TD style="padding: 2px;"></TD><TD style="padding: 2px;"></TD></TR></TBODY></TABLE></BODY></HTML> Wed, 20 Apr 2016 13:11:49 GMT Anonymous 2016-04-20T13:11:49Z Move fields over if blank https://community.qlik.com/t5/QlikView/Move-fields-over-if-blank/m-p/1068069#M638803 <HTML><HEAD></HEAD><BODY><P>Hi guys. Here's my load script</P><P></P><P>NI,</P><P>Address1,</P><P>Address2,</P><P>Address3,</P><P>Address4,</P><P>Address5,</P><P>PostCode</P><P></P><P>From <C> (ooxml, embedded labels, Table is 'Report');</C></P><P></P><P>Some of the addresses have spaces. For example</P><P></P><TABLE border="1" class="jiveBorder" style="border: 1px solid rgb(0, 0, 0); width: 100%;"><TBODY><TR><TH style="text-align: center; background-color: #6690bc; color: #ffffff; padding: 2px;" valign="middle">NI</TH><TH style="text-align: center; background-color: #6690bc; color: #ffffff; padding: 2px;" valign="middle"><STRONG>Address1</STRONG></TH><TH style="text-align: center; background-color: #6690bc; color: #ffffff; padding: 2px;" valign="middle"><STRONG><STRONG>Address2</STRONG></STRONG></TH><TH style="text-align: center; background-color: #6690bc; color: #ffffff; padding: 2px;" valign="middle"><STRONG><STRONG>Address3</STRONG></STRONG></TH><TH style="text-align: center; background-color: #6690bc; color: #ffffff; padding: 2px;" valign="middle"><STRONG><STRONG>Address4</STRONG></STRONG></TH><TH style="text-align: center; background-color: #6690bc; color: #ffffff; padding: 2px;" valign="middle"><STRONG><STRONG>Address5</STRONG></STRONG></TH></TR><TR><TD style="padding: 2px;">12345</TD><TD style="padding: 2px;">1 bob street</TD><TD style="padding: 2px;"></TD><TD style="padding: 2px;">Cheltenham</TD><TD style="padding: 2px;"></TD><TD style="padding: 2px;">South</TD></TR></TBODY></TABLE><P></P><P>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</P><P></P><TABLE border="1" class="jiveBorder" style="border: 1px solid #000000; width: 100%;"><TBODY><TR><TH style="text-align: center; background-color: #6690bc; color: #ffffff; padding: 2px;" valign="middle">NI</TH><TH style="text-align: center; background-color: #6690bc; color: #ffffff; padding: 2px;" valign="middle"><STRONG>Address1</STRONG></TH><TH style="text-align: center; background-color: #6690bc; color: #ffffff; padding: 2px;" valign="middle"><STRONG><STRONG>Address2</STRONG></STRONG></TH><TH style="text-align: center; background-color: #6690bc; color: #ffffff; padding: 2px;" valign="middle"><STRONG><STRONG>Address3</STRONG></STRONG></TH><TH style="text-align: center; background-color: #6690bc; color: #ffffff; padding: 2px;" valign="middle"><STRONG><STRONG>Address4</STRONG></STRONG></TH><TH style="text-align: center; background-color: #6690bc; color: #ffffff; padding: 2px;" valign="middle"><STRONG><STRONG>Address5</STRONG></STRONG></TH></TR><TR><TD style="padding: 2px;">12345</TD><TD style="padding: 2px;">1 bob street</TD><TD style="padding: 2px;">Cheltenham</TD><TD style="padding: 2px;">South</TD><TD style="padding: 2px;"></TD><TD style="padding: 2px;"></TD></TR></TBODY></TABLE></BODY></HTML> Wed, 20 Apr 2016 13:11:49 GMT https://community.qlik.com/t5/QlikView/Move-fields-over-if-blank/m-p/1068069#M638803 Anonymous 2016-04-20T13:11:49Z Re: Move fields over if blank https://community.qlik.com/t5/QlikView/Move-fields-over-if-blank/m-p/1068070#M638804 <HTML><HEAD></HEAD><BODY><P>May be this:</P><P></P><P><STRONG>Table:</STRONG></P><P><STRONG>LOAD NI, </STRONG></P><P><STRONG>     SubField(If(Len(Trim(Address1)) > 0, Trim(Address1)&'|')&</STRONG></P><P><STRONG>       If(Len(Trim(Address2)) > 0, Trim(Address2)&'|')&</STRONG></P><P><STRONG>       If(Len(Trim(Address3)) > 0, Trim(Address3)&'|')&</STRONG></P><P><STRONG>       If(Len(Trim(Address4)) > 0, Trim(Address4)&'|')&</STRONG></P><P><STRONG>       If(Len(Trim(Address5)) > 0, Trim(Address5)), '|', 1) as Address1,</STRONG></P><P><STRONG>     SubField(If(Len(Trim(Address1)) > 0, Trim(Address1)&'|')&</STRONG></P><P><STRONG>       If(Len(Trim(Address2)) > 0, Trim(Address2)&'|')&</STRONG></P><P><STRONG>       If(Len(Trim(Address3)) > 0, Trim(Address3)&'|')&</STRONG></P><P><STRONG>       If(Len(Trim(Address4)) > 0, Trim(Address4)&'|')&</STRONG></P><P><STRONG>       If(Len(Trim(Address5)) > 0, Trim(Address5)), '|', 2) as Address2,</STRONG></P><P><STRONG>     SubField(If(Len(Trim(Address1)) > 0, Trim(Address1)&'|')&</STRONG></P><P><STRONG>       If(Len(Trim(Address2)) > 0, Trim(Address2)&'|')&</STRONG></P><P><STRONG>       If(Len(Trim(Address3)) > 0, Trim(Address3)&'|')&</STRONG></P><P><STRONG>       If(Len(Trim(Address4)) > 0, Trim(Address4)&'|')&</STRONG></P><P><STRONG>       If(Len(Trim(Address5)) > 0, Trim(Address5)), '|', 3) as Address3,</STRONG></P><P><STRONG>     SubField(If(Len(Trim(Address1)) > 0, Trim(Address1)&'|')&</STRONG></P><P><STRONG>       If(Len(Trim(Address2)) > 0, Trim(Address2)&'|')&</STRONG></P><P><STRONG>       If(Len(Trim(Address3)) > 0, Trim(Address3)&'|')&</STRONG></P><P><STRONG>       If(Len(Trim(Address4)) > 0, Trim(Address4)&'|')&</STRONG></P><P><STRONG>       If(Len(Trim(Address5)) > 0, Trim(Address5)), '|', 4) as Address4,</STRONG></P><P><STRONG>     SubField(If(Len(Trim(Address1)) > 0, Trim(Address1)&'|')&</STRONG></P><P><STRONG>       If(Len(Trim(Address2)) > 0, Trim(Address2)&'|')&</STRONG></P><P><STRONG>       If(Len(Trim(Address3)) > 0, Trim(Address3)&'|')&</STRONG></P><P><STRONG>       If(Len(Trim(Address4)) > 0, Trim(Address4)&'|')&</STRONG></P><P><STRONG>       If(Len(Trim(Address5)) > 0, Trim(Address5)), '|', 5) as Address5</STRONG></P><P><STRONG>FROM</STRONG></P><P><STRONG><SPAN>[</SPAN><A _jive_internal="true" data-containerid="2061" data-containertype="14" data-objectid="213890" data-objecttype="1" href="https://community.qlik.com/thread/213890">https://community.qlik.com/thread/213890</A><SPAN>]</SPAN></STRONG></P><P><STRONG>(html, codepage is 1252, embedded labels, table is @1);</STRONG></P></BODY></HTML> Wed, 20 Apr 2016 13:34:43 GMT https://community.qlik.com/t5/QlikView/Move-fields-over-if-blank/m-p/1068070#M638804 sunny_talwar 2016-04-20T13:34:43Z