topic Re: Task categories Status with Log ID in one excel with multiple status as header in QlikView https://community.qlik.com/t5/QlikView/Task-categories-Status-with-Log-ID-in-one-excel-with-multiple/m-p/1033458#M930873 <HTML><HEAD></HEAD><BODY><P>Hi, do you mind show the steps?</P><P></P><P>Thanks</P></BODY></HTML> Wed, 09 Mar 2016 07:24:02 GMT nicholas5141 2016-03-09T07:24:02Z Task categories Status with Log ID in one excel with multiple status as header https://community.qlik.com/t5/QlikView/Task-categories-Status-with-Log-ID-in-one-excel-with-multiple/m-p/1033455#M930866 <HTML><HEAD></HEAD><BODY><P>Hi,</P><P></P><P>I need help on this excel with the status as header link with log ID. Is it possible to do that ?</P><P></P><P>Excepted Result:</P><P></P><P>Log ID&nbsp;&nbsp;&nbsp;&nbsp; Descriptions&nbsp;&nbsp;&nbsp;&nbsp; Date&nbsp;&nbsp;&nbsp;&nbsp; Status</P><P>1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; test1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Open&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </P><P>2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; test2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Open</P><P>3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; test3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Open</P><P style="font-size: 13.3333px;">4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; test4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WIP&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </P><P style="font-size: 13.3333px;">5&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; test5&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WIP</P><P style="font-size: 13.3333px;">6&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; test6&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WIP</P><P style="font-size: 13.3333px;">7&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; test7&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Close&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </P><P style="font-size: 13.3333px;">8&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; test8&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Close</P><P style="font-size: 13.3333px;">9&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; test9&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Close</P><P style="font-size: 13.3333px;"></P><P style="font-size: 13.3333px;">If there is any better approach please suggest!</P><P></P><P><SPAN style="font-size: 13.3333px;">Thank you</SPAN></P></BODY></HTML> Wed, 25 Nov 2020 16:16:04 GMT https://community.qlik.com/t5/QlikView/Task-categories-Status-with-Log-ID-in-one-excel-with-multiple/m-p/1033455#M930866 nicholas5141 2020-11-25T16:16:04Z Re: Task categories Status with Log ID in one excel with multiple status as header https://community.qlik.com/t5/QlikView/Task-categories-Status-with-Log-ID-in-one-excel-with-multiple/m-p/1033456#M930868 <HTML><HEAD></HEAD><BODY><P>One solution could be..</P><P></P><P style="margin-bottom: .0001pt;"><STRONG style="color: blue; font-size: 9.0pt; font-family: 'Courier New';">Directory</STRONG><SPAN style="font-size: 9.0pt; font-family: 'Courier New'; color: black;">;<BR /> T1:<BR /> </SPAN><STRONG style="color: blue; font-size: 9.0pt; font-family: 'Courier New';">LOAD</STRONG> <SPAN style="font-size: 9.0pt; font-family: 'Courier New'; color: maroon;">[Log ID]</SPAN><SPAN style="font-size: 9.0pt; font-family: 'Courier New'; color: black;">, <BR /> </SPAN><SPAN style="font-size: 9.0pt; font-family: 'Courier New'; color: maroon;">Descriptions</SPAN><SPAN style="font-size: 9.0pt; font-family: 'Courier New'; color: black;">, <BR /> </SPAN><SPAN style="font-size: 9.0pt; font-family: 'Courier New'; color: maroon;">Date</SPAN><SPAN style="font-size: 9.0pt; font-family: 'Courier New'; color: black;">,<BR /> </SPAN><SPAN style="font-size: 9.0pt; font-family: 'Courier New'; color: blue;">if</SPAN><SPAN style="font-size: 9.0pt; font-family: 'Courier New'; color: black;">(</SPAN><SPAN style="font-size: 9.0pt; font-family: 'Courier New'; color: blue;">WildMatch</SPAN><SPAN style="font-size: 9.0pt; font-family: 'Courier New'; color: black;">(</SPAN><SPAN style="font-size: 9.0pt; font-family: 'Courier New'; color: maroon;">[Log ID]</SPAN><SPAN style="font-size: 9.0pt; font-family: 'Courier New'; color: black;">,'*Status*'),</SPAN><SPAN style="font-size: 9.0pt; font-family: 'Courier New'; color: blue;">SubField</SPAN><SPAN style="font-size: 9.0pt; font-family: 'Courier New'; color: black;">(</SPAN><SPAN style="font-size: 9.0pt; font-family: 'Courier New'; color: maroon;">[Log ID]</SPAN><SPAN style="font-size: 9.0pt; font-family: 'Courier New'; color: black;">,': ',2),</SPAN><SPAN style="font-size: 9.0pt; font-family: 'Courier New'; color: blue;">Peek</SPAN><SPAN style="font-size: 9.0pt; font-family: 'Courier New'; color: black;">(</SPAN><SPAN style="font-size: 9.0pt; font-family: 'Courier New'; color: maroon;">Status</SPAN><SPAN style="font-size: 9.0pt; font-family: 'Courier New'; color: black;">)) </SPAN><SPAN style="font-size: 9.0pt; font-family: 'Courier New'; color: blue;">as</SPAN> <SPAN style="font-size: 9.0pt; font-family: 'Courier New'; color: maroon;">Status</SPAN><SPAN style="font-size: 9.0pt; font-family: 'Courier New'; color: black;"><BR /> </SPAN><SPAN style="font-size: 9.0pt; font-family: 'Courier New'; color: blue;">FROM</SPAN><SPAN style="font-size: 9.0pt; font-family: 'Courier New'; color: black;"><BR /> QlikCommunityFile.xlsx<BR /> (</SPAN><SPAN style="font-size: 9.0pt; font-family: 'Courier New'; color: blue;">ooxml</SPAN><SPAN style="font-size: 9.0pt; font-family: 'Courier New'; color: black;">, </SPAN><SPAN style="font-size: 9.0pt; font-family: 'Courier New'; color: blue;">embedded</SPAN> <SPAN style="font-size: 9.0pt; font-family: 'Courier New'; color: blue;">labels</SPAN><SPAN style="font-size: 9.0pt; font-family: 'Courier New'; color: black;">, </SPAN><SPAN style="font-size: 9.0pt; font-family: 'Courier New'; color: blue;">header</SPAN> <SPAN style="font-size: 9.0pt; font-family: 'Courier New'; color: blue;">is</SPAN><SPAN style="font-size: 9.0pt; font-family: 'Courier New'; color: black;"> 1 </SPAN><SPAN style="font-size: 9.0pt; font-family: 'Courier New'; color: blue;">lines</SPAN><SPAN style="font-size: 9.0pt; font-family: 'Courier New'; color: black;">, </SPAN><SPAN style="font-size: 9.0pt; font-family: 'Courier New'; color: blue;">table</SPAN> <SPAN style="font-size: 9.0pt; font-family: 'Courier New'; color: blue;">is</SPAN><SPAN style="font-size: 9.0pt; font-family: 'Courier New'; color: black;"> Sheet1);<BR /> <BR /> </SPAN><SPAN style="font-size: 9.0pt; font-family: 'Courier New'; color: blue;">NoConcatenate</SPAN><SPAN style="font-size: 9.0pt; font-family: 'Courier New'; color: black;"><BR /> T2:<BR /> </SPAN><STRONG style="color: blue; font-size: 9.0pt; font-family: 'Courier New';">LOAD</STRONG><SPAN style="font-size: 9.0pt; font-family: 'Courier New'; color: black;"> * </SPAN><SPAN style="font-size: 9.0pt; font-family: 'Courier New'; color: blue;">Resident</SPAN><SPAN style="font-size: 9.0pt; font-family: 'Courier New'; color: black;"> T1 </SPAN><SPAN style="font-size: 9.0pt; font-family: 'Courier New'; color: blue;">Where</SPAN> <SPAN style="font-size: 9.0pt; font-family: 'Courier New'; color: blue;">not</SPAN> <SPAN style="font-size: 9.0pt; font-family: 'Courier New'; color: blue;">IsNull</SPAN><SPAN style="font-size: 9.0pt; font-family: 'Courier New'; color: black;">(</SPAN><SPAN style="font-size: 9.0pt; font-family: 'Courier New'; color: maroon;">Descriptions</SPAN><SPAN style="font-size: 9.0pt; font-family: 'Courier New'; color: black;">);<BR /> <BR /> </SPAN><STRONG style="color: blue; font-size: 9.0pt; font-family: 'Courier New';">DROP</STRONG> <SPAN style="font-size: 9.0pt; font-family: 'Courier New'; color: blue;">Table</SPAN><SPAN style="font-size: 9.0pt; font-family: 'Courier New'; color: black;"> T1; </SPAN></P><P></P><P><IMG alt="Capture.JPG" class="jive-image image-1" src="https://community.qlik.com/legacyfs/online/117444_Capture.JPG" style="height: auto;" /></P></BODY></HTML> Wed, 09 Mar 2016 03:54:28 GMT https://community.qlik.com/t5/QlikView/Task-categories-Status-with-Log-ID-in-one-excel-with-multiple/m-p/1033456#M930868 settu_periasamy 2016-03-09T03:54:28Z Re: Task categories Status with Log ID in one excel with multiple status as header https://community.qlik.com/t5/QlikView/Task-categories-Status-with-Log-ID-in-one-excel-with-multiple/m-p/1033457#M930871 <HTML><HEAD></HEAD><BODY><P>You can also use transformation wizard to achieve the same.</P><P></P><P><SPAN style="font-size: 9.0pt; font-family: 'Courier New'; color: black;"><SPAN style="font-size: 10pt;">T1:</SPAN><BR /> </SPAN><SPAN style="font-size: 10pt;"><SPAN style="color: #0080ff; font-family: 'Courier New';"><STRONG>LOAD</STRONG></SPAN> <SPAN style="font-family: 'Courier New'; color: #ff0080;">[Log ID]</SPAN><SPAN style="font-family: 'Courier New'; color: black;">, <BR /> </SPAN><SPAN style="font-family: 'Courier New'; color: #ff0080;">Descriptions</SPAN><SPAN style="font-family: 'Courier New'; color: black;">, <BR /> </SPAN><SPAN style="font-family: 'Courier New'; color: blue;">Date</SPAN><SPAN style="font-family: 'Courier New'; color: black;">(</SPAN><SPAN style="font-family: 'Courier New'; color: #ff0080;">Date</SPAN><SPAN style="font-family: 'Courier New'; color: black;">) </SPAN><SPAN style="color: #0080ff; font-family: 'Courier New';"><STRONG>as</STRONG></SPAN> <SPAN style="font-family: 'Courier New'; color: #ff0080;">Date</SPAN><SPAN style="font-family: 'Courier New'; color: black;">, <BR /> </SPAN><SPAN style="font-family: 'Courier New'; color: blue;">Replace</SPAN><SPAN style="font-family: 'Courier New'; color: black;">(</SPAN><SPAN style="font-family: 'Courier New'; color: #ff0080;">F4</SPAN><SPAN style="font-family: 'Courier New'; color: black;">,'Status: ','') </SPAN><SPAN style="color: #0080ff; font-family: 'Courier New';"><STRONG>as</STRONG></SPAN> <SPAN style="font-family: 'Courier New'; color: #ff0080;">Status</SPAN><SPAN style="font-family: 'Courier New'; color: black;"><BR /> </SPAN><SPAN style="color: #0080ff; font-family: 'Courier New';"><STRONG>FROM</STRONG></SPAN><SPAN style="font-family: 'Courier New'; color: black;"><BR /> QlikCommunityFile.xlsx<BR /> (</SPAN><SPAN style="color: #0080ff; font-family: 'Courier New';"><STRONG>ooxml</STRONG></SPAN><SPAN style="font-family: 'Courier New'; color: black;">, </SPAN><SPAN style="color: #0080ff; font-family: 'Courier New';"><STRONG>embedded</STRONG></SPAN> <SPAN style="color: #0080ff; font-family: 'Courier New';"><STRONG>labels</STRONG></SPAN><SPAN style="font-family: 'Courier New'; color: black;">, </SPAN><SPAN style="color: #0080ff; font-family: 'Courier New';"><STRONG>header</STRONG></SPAN> <SPAN style="color: #0080ff; font-family: 'Courier New';"><STRONG>is</STRONG></SPAN><SPAN style="font-family: 'Courier New'; color: black;"> 1 </SPAN><SPAN style="color: #0080ff; font-family: 'Courier New';"><STRONG>lines</STRONG></SPAN><SPAN style="font-family: 'Courier New'; color: black;">, </SPAN><SPAN style="color: #0080ff; font-family: 'Courier New';"><STRONG>table</STRONG></SPAN> <SPAN style="color: #0080ff; font-family: 'Courier New';"><STRONG>is</STRONG></SPAN><SPAN style="font-family: 'Courier New'; color: black;"> Sheet1, </SPAN><SPAN style="color: #0080ff; font-family: 'Courier New';"><STRONG>filters</STRONG></SPAN><SPAN style="font-family: 'Courier New'; color: black;">(<BR /> </SPAN><SPAN style="color: #0080ff; font-family: 'Courier New';"><STRONG>ColXtr</STRONG></SPAN><SPAN style="font-family: 'Courier New'; color: black;">(1, </SPAN><SPAN style="color: #0080ff; font-family: 'Courier New';"><STRONG>RowCnd</STRONG></SPAN><SPAN style="font-family: 'Courier New'; color: black;">(</SPAN><SPAN style="color: #0080ff; font-family: 'Courier New';"><STRONG>CellValue</STRONG></SPAN><SPAN style="font-family: 'Courier New'; color: black;">, 1, </SPAN><SPAN style="color: #0080ff; font-family: 'Courier New';"><STRONG>StrCnd</STRONG></SPAN><SPAN style="font-family: 'Courier New'; color: black;">(</SPAN><SPAN style="color: #0080ff; font-family: 'Courier New';"><STRONG>start</STRONG></SPAN><SPAN style="font-family: 'Courier New'; color: black;">, 'Status:')), 0),<BR /> </SPAN><SPAN style="color: #0080ff; font-family: 'Courier New';"><STRONG>Replace</STRONG></SPAN><SPAN style="font-family: 'Courier New'; color: black;">(4, </SPAN><SPAN style="color: #0080ff; font-family: 'Courier New';"><STRONG>top</STRONG></SPAN><SPAN style="font-family: 'Courier New'; color: black;">, </SPAN><SPAN style="color: #0080ff; font-family: 'Courier New';"><STRONG>StrCnd</STRONG></SPAN><SPAN style="font-family: 'Courier New'; color: black;">(</SPAN><SPAN style="color: #0080ff; font-family: 'Courier New';"><STRONG>null</STRONG></SPAN><SPAN style="font-family: 'Courier New'; color: black;">)),<BR /> </SPAN><SPAN style="color: #0080ff; font-family: 'Courier New';"><STRONG>Remove</STRONG></SPAN><SPAN style="font-family: 'Courier New'; color: black;">(</SPAN><SPAN style="color: #0080ff; font-family: 'Courier New';"><STRONG>Row</STRONG></SPAN><SPAN style="font-family: 'Courier New'; color: black;">, </SPAN><SPAN style="color: #0080ff; font-family: 'Courier New';"><STRONG>RowCnd</STRONG></SPAN><SPAN style="font-family: 'Courier New'; color: black;">(</SPAN><SPAN style="color: #0080ff; font-family: 'Courier New';"><STRONG>CellValue</STRONG></SPAN><SPAN style="font-family: 'Courier New'; color: black;">, 1, </SPAN><SPAN style="color: #0080ff; font-family: 'Courier New';"><STRONG>StrCnd</STRONG></SPAN><SPAN style="font-family: 'Courier New'; color: black;">(</SPAN><SPAN style="color: #0080ff; font-family: 'Courier New';"><STRONG>start</STRONG></SPAN></SPAN><SPAN style="font-size: 9.0pt; font-family: 'Courier New'; color: black;"><SPAN style="font-size: 10pt;">, 'Status:')))</SPAN><BR /><SPAN style="font-size: 10pt;"> ));</SPAN></SPAN></P><P><SPAN style="color: black; font-size: 10pt; font-family: 'Courier New';"><BR /></SPAN></P><P><SPAN style="color: black; font-size: 10pt; font-family: 'Courier New';"><IMG alt="Capture.PNG" class="jive-image image-1" src="https://community.qlik.com/legacyfs/online/117452_Capture.PNG" style="height: auto;" /><BR /></SPAN></P></BODY></HTML> Wed, 09 Mar 2016 04:45:57 GMT https://community.qlik.com/t5/QlikView/Task-categories-Status-with-Log-ID-in-one-excel-with-multiple/m-p/1033457#M930871 tamilarasu 2016-03-09T04:45:57Z Re: Task categories Status with Log ID in one excel with multiple status as header https://community.qlik.com/t5/QlikView/Task-categories-Status-with-Log-ID-in-one-excel-with-multiple/m-p/1033458#M930873 <HTML><HEAD></HEAD><BODY><P>Hi, do you mind show the steps?</P><P></P><P>Thanks</P></BODY></HTML> Wed, 09 Mar 2016 07:24:02 GMT https://community.qlik.com/t5/QlikView/Task-categories-Status-with-Log-ID-in-one-excel-with-multiple/m-p/1033458#M930873 nicholas5141 2016-03-09T07:24:02Z Re: Task categories Status with Log ID in one excel with multiple status as header https://community.qlik.com/t5/QlikView/Task-categories-Status-with-Log-ID-in-one-excel-with-multiple/m-p/1033459#M930875 <HTML><HEAD></HEAD><BODY><P>Sure. I just pasted the screenshots in a word file and attached the same. <SPAN style="font-size: 10pt; line-height: 1.5em;">If you find any difficulties, let me know.</SPAN></P></BODY></HTML> Wed, 09 Mar 2016 13:01:46 GMT https://community.qlik.com/t5/QlikView/Task-categories-Status-with-Log-ID-in-one-excel-with-multiple/m-p/1033459#M930875 tamilarasu 2016-03-09T13:01:46Z Re: Task categories Status with Log ID in one excel with multiple status as header https://community.qlik.com/t5/QlikView/Task-categories-Status-with-Log-ID-in-one-excel-with-multiple/m-p/1033460#M930877 <HTML><HEAD></HEAD><BODY><P>Thanks! I got It</P></BODY></HTML> Thu, 10 Mar 2016 00:48:20 GMT https://community.qlik.com/t5/QlikView/Task-categories-Status-with-Log-ID-in-one-excel-with-multiple/m-p/1033460#M930877 nicholas5141 2016-03-10T00:48:20Z