topic Re: Extract the data from a subfiled in QlikView https://community.qlik.com/t5/QlikView/Extract-the-data-from-a-subfiled/m-p/1322041#M827669 <HTML><HEAD></HEAD><BODY><P>Hi Constantin,</P><P></P><P></P><P>Try:</P><P></P><P>Table:</P><P>Load</P><P>if(WildMatch(Field,'*|*'),Trim(Right(Field,len(Field) - Index(Field,'|'))),Field) as Field;</P><P></P><P>LOAD * Inline [</P><P>Field</P><P>aaaaa</P><P>bbbbb</P><P>test1| test 2| test3| Good</P><P>test1| test10| test 2| very good</P><P>test3| test 2| test3| good</P><P>test5| test 2| test3| Good</P><P>test16 test 2| test3| good</P><P>];</P><P></P><P>gives:</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>Field</STRONG></SPAN><STRONG> </STRONG></TH></TR><TR bgcolor="#ffffff"><TD bgcolor="#ffffff"><SPAN style="color: #363636;">aaaaa</SPAN> </TD></TR><TR bgcolor="#ffffff"><TD bgcolor="#ffffff"><SPAN style="color: #363636;">bbbbb</SPAN> </TD></TR><TR bgcolor="#ffffff"><TD bgcolor="#ffffff"><SPAN style="color: #363636;">test 2| test3| Good</SPAN> </TD></TR><TR bgcolor="#ffffff"><TD bgcolor="#ffffff"><SPAN style="color: #363636;">test 2| test3| good</SPAN> </TD></TR><TR bgcolor="#ffffff"><TD bgcolor="#ffffff"><SPAN style="color: #363636;">test3| good</SPAN> </TD></TR><TR bgcolor="#ffffff"><TD bgcolor="#ffffff"><SPAN style="color: #363636;">test10| test 2| very good</SPAN></TD></TR></TBODY></TABLE><P></P><P>Cheers</P><P></P><P>Andrew</P></BODY></HTML> Tue, 04 Jul 2017 08:56:59 GMT effinty2112 2017-07-04T08:56:59Z Extract the data from a subfiled https://community.qlik.com/t5/QlikView/Extract-the-data-from-a-subfiled/m-p/1322038#M827666 <HTML><HEAD></HEAD><BODY><P>Hi all,</P><P></P><P>tab1:</P><P>id,</P><P><SPAN style="font-size: 13.3333px;">description </SPAN></P><P>from test.qvd</P><P></P><P>The description field contain data like this:</P><P>aaaaa,</P><P>bbbbb,</P><P>test1| test 2| test3| Good</P><P>test1| test10| test 2| very good</P><P><SPAN style="font-size: 13.3333px;">test3| test 2| test3| good</SPAN></P><P><SPAN style="font-size: 13.3333px;">test5| test 2| test3| Good</SPAN></P><P><SPAN style="font-size: 13.3333px;">test16 test 2| test3| good</SPAN></P><P><SPAN style="font-size: 13.3333px;"><BR /></SPAN></P><P><SPAN style="font-size: 13.3333px;">I want to extract the data starting after the first&nbsp;&nbsp; ' |&nbsp; ' could&nbsp; you please help?</SPAN></P><P><SPAN style="font-size: 13.3333px;"><BR /></SPAN></P><P>I used this :</P><P></P><P>if(trim(subfield(SubField(<SPAN style="font-size: 13.3333px;">description </SPAN>,'|',-1),'(',1))='good','Good',<SPAN style="font-size: 13.3333px;">description </SPAN>) as [Sub Category],</P><P></P><P>But after this extraction I still have something like :</P><P style="font-size: 13.3333px;">aaaaa,</P><P style="font-size: 13.3333px;">bbbbb,</P><P style="font-size: 13.3333px;">test1| test 2| test3| Good</P><P style="font-size: 13.3333px;">test1| test10| test 2| very good</P><P style="font-size: 13.3333px;"><SPAN style="font-size: 13.3333px;">test3| test 2| test5| Good</SPAN></P><P style="font-size: 13.3333px;"><SPAN style="font-size: 13.3333px;">test5| test 2| test3| Good</SPAN></P><P style="font-size: 13.3333px;"><SPAN style="font-size: 13.3333px;">test16 <SPAN style="font-size: 13.3333px;">test 2| test4| Good</SPAN></SPAN></P><P style="font-size: 13.3333px;"><SPAN style="font-size: 13.3333px;"><BR /></SPAN></P><P style="font-size: 13.3333px;"><SPAN style="font-size: 13.3333px;">I want to see&nbsp; this:</SPAN></P><P style="font-size: 13.3333px;">aaaaa,</P><P style="font-size: 13.3333px;">bbbbb,</P><P style="font-size: 13.3333px;"><SPAN style="font-size: 13.3333px;">test 2| test3| Good</SPAN></P><P style="font-size: 13.3333px;"><SPAN style="font-size: 13.3333px;">test10| test 2| very good</SPAN></P><P>t<SPAN style="font-size: 13.3333px;">est 2| test5| Good</SPAN></P><P><SPAN style="font-size: 13.3333px;">test 2| test3| Good</SPAN></P><P><SPAN style="font-size: 13.3333px;">test 2| test4| Good</SPAN></P></BODY></HTML> Wed, 25 Nov 2020 16:16:04 GMT https://community.qlik.com/t5/QlikView/Extract-the-data-from-a-subfiled/m-p/1322038#M827666 0li5a3a 2020-11-25T16:16:04Z Re: Extract the data from a subfiled https://community.qlik.com/t5/QlikView/Extract-the-data-from-a-subfiled/m-p/1322039#M827667 <HTML><HEAD></HEAD><BODY><P><SPAN style="color: #ff0000;">&lt;&lt;correction&gt;&gt;</SPAN></P><P></P><P>load <STRONG style="text-decoration: underline;">mid(f2,index(f2,'|',1)+1,len(f2)) as SubCategory</STRONG> Inline [</P><P>f2</P><P>aaaaa</P><P>bbbb</P><P>test1| test 2| test3| Good</P><P>test1| test10| test 2| very good</P><P>test3| test 2| test5| Good</P><P>test5| test 2| test3| Good</P><P>test16| test 2| test4| Good</P><P>];</P></BODY></HTML> Tue, 04 Jul 2017 08:43:16 GMT https://community.qlik.com/t5/QlikView/Extract-the-data-from-a-subfiled/m-p/1322039#M827667 vinieme12 2017-07-04T08:43:16Z Re: Extract the data from a subfiled https://community.qlik.com/t5/QlikView/Extract-the-data-from-a-subfiled/m-p/1322040#M827668 <HTML><HEAD></HEAD><BODY><P>Hi Constantin,</P><P>maybe</P><P></P><P><SPAN style="color: #0000ff; font-size: 8pt;"><STRONG style="font-size: 10pt;">LOAD</STRONG></SPAN><SPAN style="font-size: 10pt;"> <SPAN style="color: #800000;">id</SPAN>,<SPAN style="color: #0000ff;">If</SPAN>(<SPAN style="color: #0000ff;">Index</SPAN>(<SPAN style="color: #800000;">description</SPAN>,'|',1)&gt;0,<SPAN style="color: #0000ff;">Mid</SPAN>(<SPAN style="color: #800000;">description</SPAN>,<SPAN style="color: #0000ff;">Index</SPAN>(<SPAN style="color: #800000;">description</SPAN>,'|',1)+1),<SPAN style="color: #800000;">description</SPAN>) <SPAN style="color: #0000ff;">as</SPAN> <SPAN style="color: #800000;">description</SPAN> <BR /> <SPAN style="color: #0000ff;">Inline</SPAN> <SPAN style="color: #800000;">[<BR /> id,description<BR /> 1,aaaaa,<BR /> 2,bbbbb,<BR /> 3,test1| test 2| test3| Good<BR /> 4,test1| test10| test 2| very good<BR /> 5,test3| test 2| test3| good<BR /> 6,test5| test 2| test3| Good<BR /> 7,test16| test 2| test3| good<BR /> ]</SPAN>; </SPAN></P><P><SPAN style="font-size: 10pt;"><IMG class="jive-image image-1" src="https://community.qlik.com/legacyfs/online/168983_pastedImage_1.png" style="max-height: 900px; max-width: 1200px;" /></SPAN></P><P><SPAN style="font-size: 10pt;">Regards,</SPAN></P><P><SPAN style="font-size: 10pt;">Antonio</SPAN></P></BODY></HTML> Tue, 04 Jul 2017 08:48:30 GMT https://community.qlik.com/t5/QlikView/Extract-the-data-from-a-subfiled/m-p/1322040#M827668 antoniotiman 2017-07-04T08:48:30Z Re: Extract the data from a subfiled https://community.qlik.com/t5/QlikView/Extract-the-data-from-a-subfiled/m-p/1322041#M827669 <HTML><HEAD></HEAD><BODY><P>Hi Constantin,</P><P></P><P></P><P>Try:</P><P></P><P>Table:</P><P>Load</P><P>if(WildMatch(Field,'*|*'),Trim(Right(Field,len(Field) - Index(Field,'|'))),Field) as Field;</P><P></P><P>LOAD * Inline [</P><P>Field</P><P>aaaaa</P><P>bbbbb</P><P>test1| test 2| test3| Good</P><P>test1| test10| test 2| very good</P><P>test3| test 2| test3| good</P><P>test5| test 2| test3| Good</P><P>test16 test 2| test3| good</P><P>];</P><P></P><P>gives:</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>Field</STRONG></SPAN><STRONG> </STRONG></TH></TR><TR bgcolor="#ffffff"><TD bgcolor="#ffffff"><SPAN style="color: #363636;">aaaaa</SPAN> </TD></TR><TR bgcolor="#ffffff"><TD bgcolor="#ffffff"><SPAN style="color: #363636;">bbbbb</SPAN> </TD></TR><TR bgcolor="#ffffff"><TD bgcolor="#ffffff"><SPAN style="color: #363636;">test 2| test3| Good</SPAN> </TD></TR><TR bgcolor="#ffffff"><TD bgcolor="#ffffff"><SPAN style="color: #363636;">test 2| test3| good</SPAN> </TD></TR><TR bgcolor="#ffffff"><TD bgcolor="#ffffff"><SPAN style="color: #363636;">test3| good</SPAN> </TD></TR><TR bgcolor="#ffffff"><TD bgcolor="#ffffff"><SPAN style="color: #363636;">test10| test 2| very good</SPAN></TD></TR></TBODY></TABLE><P></P><P>Cheers</P><P></P><P>Andrew</P></BODY></HTML> Tue, 04 Jul 2017 08:56:59 GMT https://community.qlik.com/t5/QlikView/Extract-the-data-from-a-subfiled/m-p/1322041#M827669 effinty2112 2017-07-04T08:56:59Z Re: Extract the data from a subfiled https://community.qlik.com/t5/QlikView/Extract-the-data-from-a-subfiled/m-p/1322042#M827670 <HTML><HEAD></HEAD><BODY><P>Well done! Many Thanks</P></BODY></HTML> Tue, 04 Jul 2017 09:03:20 GMT https://community.qlik.com/t5/QlikView/Extract-the-data-from-a-subfiled/m-p/1322042#M827670 0li5a3a 2017-07-04T09:03:20Z