topic Re: How to remove text after a specific character ('.' or ',' or '-') in QlikView https://community.qlik.com/t5/QlikView/How-to-remove-text-after-a-specific-character-or-or/m-p/1162249#M897973 <HTML><HEAD></HEAD><BODY><P>May be try this:</P><P></P><P><STRONG>Table:</STRONG></P><P><STRONG>LOAD *,</STRONG></P><P><STRONG>&nbsp; <SPAN style="color: #ff0000;">Left(Number, FindOneOf(Number &amp; '.', '.,-')-1) as NewNumber</SPAN>;</STRONG></P><P><STRONG>LOAD * Inline [</STRONG></P><P><STRONG>Number</STRONG></P><P><STRONG>237589</STRONG></P><P><STRONG>573830.098.9</STRONG></P><P><STRONG>98374,09</STRONG></P><P><STRONG>987464-1</STRONG></P><P><STRONG>84736.999.1</STRONG></P><P><STRONG>98383</STRONG></P><P><STRONG>6444-87</STRONG></P><P><STRONG>];</STRONG></P><P><STRONG><BR /></STRONG></P><P><IMG alt="Capture.PNG" class="jive-image image-1" src="https://community.qlik.com/legacyfs/online/132748_Capture.PNG" style="height: auto;" /></P></BODY></HTML> Wed, 27 Jul 2016 20:17:51 GMT sunny_talwar 2016-07-27T20:17:51Z How to remove text after a specific character ('.' or ',' or '-') https://community.qlik.com/t5/QlikView/How-to-remove-text-after-a-specific-character-or-or/m-p/1162246#M897970 <HTML><HEAD></HEAD><BODY><P>Hello,</P><P></P><P>Let's say I have a field with reference numbers.</P><P>237589</P><P>573830.098.9</P><P>98374.09</P><P>987464.1</P><P>84736.999.1</P><P>98383</P><P>6444.87</P><P></P><P>I would like to keep only the numbers before the first dot (.)</P><P style="font-size: 13.3333px;">237589</P><P style="font-size: 13.3333px;">573830<SPAN style="text-decoration: line-through;">.098.9</SPAN></P><P style="font-size: 13.3333px;">98374<SPAN style="text-decoration: line-through;">.09</SPAN></P><P style="font-size: 13.3333px;">987464<SPAN style="text-decoration: line-through;">.1</SPAN></P><P style="font-size: 13.3333px;">84736<SPAN style="text-decoration: line-through;">.999.1</SPAN></P><P style="font-size: 13.3333px;">98383</P><P style="font-size: 13.3333px;">6444<SPAN style="text-decoration: line-through;">.87</SPAN></P><P style="font-size: 13.3333px;"></P><P style="font-size: 13.3333px;">Thank you</P></BODY></HTML> Wed, 25 Nov 2020 16:16:04 GMT https://community.qlik.com/t5/QlikView/How-to-remove-text-after-a-specific-character-or-or/m-p/1162246#M897970 2020-11-25T16:16:04Z Re: How to remove text after a specific character ('.' or ',' or '-') https://community.qlik.com/t5/QlikView/How-to-remove-text-after-a-specific-character-or-or/m-p/1162247#M897971 <HTML><HEAD></HEAD><BODY><P>Try using the Subfield() function:</P><P></P><P></P><P></P><P>LOAD *,</P><P><STRONG>Subfield(Test,'.',1) as Extract</STRONG></P><P>INLINE [</P><P>Test</P><P>237589</P><P>573830.098.9</P><P>98374.09</P><P>987464.1</P><P>84736.999.1</P><P>98383</P><P>6444.87</P><P>];</P><P></P><TABLE bgcolor="#ffffff" border="1" cellpadding="2" cellspacing="0" style="font-size: 9pt;"><TBODY><TR bgcolor="#f5f5f5"><TH nowrap="nowrap"><SPAN style="color: #363636;"><STRONG>Test</STRONG></SPAN><STRONG> </STRONG></TH><TH nowrap="nowrap"><SPAN style="color: #363636;"><STRONG>Extract</STRONG></SPAN><STRONG> </STRONG></TH></TR><TR bgcolor="#ffffff"><TD bgcolor="#ffffff"><SPAN style="color: #363636;">6444.87</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">6444</SPAN> </TD></TR><TR bgcolor="#ffffff"><TD bgcolor="#ffffff"><SPAN style="color: #363636;">84736.999.1</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">84736</SPAN> </TD></TR><TR bgcolor="#ffffff"><TD bgcolor="#ffffff"><SPAN style="color: #363636;">98374.09</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">98374</SPAN> </TD></TR><TR bgcolor="#ffffff"><TD bgcolor="#ffffff"><SPAN style="color: #363636;">573830.098.9</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">573830</SPAN> </TD></TR><TR bgcolor="#ffffff"><TD bgcolor="#ffffff"><SPAN style="color: #363636;">987464.1</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">987464</SPAN> </TD></TR><TR bgcolor="#ffffff"><TD bgcolor="#ffffff"><SPAN style="color: #363636;">98383</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">98383</SPAN> </TD></TR><TR bgcolor="#ffffff"><TD bgcolor="#ffffff"><SPAN style="color: #363636;">237589</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">237589</SPAN></TD></TR></TBODY></TABLE></BODY></HTML> Wed, 27 Jul 2016 20:09:35 GMT https://community.qlik.com/t5/QlikView/How-to-remove-text-after-a-specific-character-or-or/m-p/1162247#M897971 swuehl 2016-07-27T20:09:35Z Re: How to remove text after a specific character ('.' or ',' or '-') https://community.qlik.com/t5/QlikView/How-to-remove-text-after-a-specific-character-or-or/m-p/1162248#M897972 <HTML><HEAD></HEAD><BODY><P>Thank you Stefan</P></BODY></HTML> Wed, 27 Jul 2016 20:17:43 GMT https://community.qlik.com/t5/QlikView/How-to-remove-text-after-a-specific-character-or-or/m-p/1162248#M897972 2016-07-27T20:17:43Z Re: How to remove text after a specific character ('.' or ',' or '-') https://community.qlik.com/t5/QlikView/How-to-remove-text-after-a-specific-character-or-or/m-p/1162249#M897973 <HTML><HEAD></HEAD><BODY><P>May be try this:</P><P></P><P><STRONG>Table:</STRONG></P><P><STRONG>LOAD *,</STRONG></P><P><STRONG>&nbsp; <SPAN style="color: #ff0000;">Left(Number, FindOneOf(Number &amp; '.', '.,-')-1) as NewNumber</SPAN>;</STRONG></P><P><STRONG>LOAD * Inline [</STRONG></P><P><STRONG>Number</STRONG></P><P><STRONG>237589</STRONG></P><P><STRONG>573830.098.9</STRONG></P><P><STRONG>98374,09</STRONG></P><P><STRONG>987464-1</STRONG></P><P><STRONG>84736.999.1</STRONG></P><P><STRONG>98383</STRONG></P><P><STRONG>6444-87</STRONG></P><P><STRONG>];</STRONG></P><P><STRONG><BR /></STRONG></P><P><IMG alt="Capture.PNG" class="jive-image image-1" src="https://community.qlik.com/legacyfs/online/132748_Capture.PNG" style="height: auto;" /></P></BODY></HTML> Wed, 27 Jul 2016 20:17:51 GMT https://community.qlik.com/t5/QlikView/How-to-remove-text-after-a-specific-character-or-or/m-p/1162249#M897973 sunny_talwar 2016-07-27T20:17:51Z Re: How to remove text after a specific character ('.' or ',' or '-') https://community.qlik.com/t5/QlikView/How-to-remove-text-after-a-specific-character-or-or/m-p/1162250#M897974 <HTML><HEAD></HEAD><BODY><P>Try this:</P><P></P><P>LOAD *, </P><P>&nbsp;&nbsp; IF(Index(Num,'.')&gt;=1, Subfield(LEFT(Num, Index(Num, '.')), '.'), Num) AS Number;</P><P>LOAD * INLINE [</P><P>Num</P><P>237589</P><P>573830.098.9</P><P>98374.09</P><P>987464.1</P><P>84736.999.1</P><P>98383</P><P>6444.87</P><P>];</P></BODY></HTML> Wed, 27 Jul 2016 20:19:21 GMT https://community.qlik.com/t5/QlikView/How-to-remove-text-after-a-specific-character-or-or/m-p/1162250#M897974 vishsaggi 2016-07-27T20:19:21Z