topic Re: Problem Transforming Data in QlikView https://community.qlik.com/t5/QlikView/Problem-Transforming-Data/m-p/802651#M668529 <HTML><HEAD></HEAD><BODY><P>You're welcome</P><P></P><P>regards</P><P></P><P>Marco</P></BODY></HTML> Sun, 23 Nov 2014 23:33:33 GMT MarcoWedel 2014-11-23T23:33:33Z Problem Transforming Data https://community.qlik.com/t5/QlikView/Problem-Transforming-Data/m-p/802648#M668526 <HTML><HEAD></HEAD><BODY><P>Hi</P><P></P><P>I have this format of input data. Which has a Header rows and Detail rows.&nbsp; It comes in this order and also has a Row number to sort the data if needed.</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>Row</STRONG></SPAN><STRONG> </STRONG></TH><TH nowrap="nowrap"><SPAN style="color: #363636;"><STRONG>Description</STRONG></SPAN><STRONG> </STRONG></TH><TH nowrap="nowrap"><SPAN style="color: #363636;"><STRONG>Type</STRONG></SPAN><STRONG> </STRONG></TH><TH nowrap="nowrap"><SPAN style="color: #363636;"><STRONG>Value</STRONG></SPAN><STRONG> </STRONG></TH></TR><TR bgcolor="#ffffff"><TD bgcolor="#ffffff"><SPAN style="color: #363636;">1</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">HA</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">Header</SPAN></TD><TD bgcolor="#ffffff"> </TD></TR><TR bgcolor="#ffffff"><TD bgcolor="#ffffff"><SPAN style="color: #363636;">2</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">a</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">Detail</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">1</SPAN> </TD></TR><TR bgcolor="#ffffff"><TD bgcolor="#ffffff"><SPAN style="color: #363636;">3</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">b</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">Detail</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">2</SPAN> </TD></TR><TR bgcolor="#ffffff"><TD bgcolor="#ffffff"><SPAN style="color: #363636;">4</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">c</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">Detail</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">3</SPAN> </TD></TR><TR bgcolor="#ffffff"><TD bgcolor="#ffffff"><SPAN style="color: #363636;">5</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">d</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">Detail</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">4</SPAN> </TD></TR><TR bgcolor="#ffffff"><TD bgcolor="#ffffff"><SPAN style="color: #363636;">6</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">HB</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">Header</SPAN></TD><TD bgcolor="#ffffff"> </TD></TR><TR bgcolor="#ffffff"><TD bgcolor="#ffffff"><SPAN style="color: #363636;">7</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">e</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">Detail</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">5</SPAN> </TD></TR><TR bgcolor="#ffffff"><TD bgcolor="#ffffff"><SPAN style="color: #363636;">8</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">HC</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">Header</SPAN></TD><TD bgcolor="#ffffff"> </TD></TR><TR bgcolor="#ffffff"><TD bgcolor="#ffffff"><SPAN style="color: #363636;">9</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">f</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">Detail</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">6</SPAN> </TD></TR><TR bgcolor="#ffffff"><TD bgcolor="#ffffff"><SPAN style="color: #363636;">10</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">g</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">Detail</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">7</SPAN> </TD></TR><TR bgcolor="#ffffff"><TD bgcolor="#ffffff"><SPAN style="color: #363636;">11</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">h</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">Detail</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">8</SPAN> </TD></TR><TR bgcolor="#ffffff"><TD bgcolor="#ffffff"><SPAN style="color: #363636;">12</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">i</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">Detail</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">9</SPAN></TD></TR></TBODY></TABLE><P></P><P><BR />And I would like to transform it into this format, such that the the Header Description is repeated until a new Header.</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>Row</STRONG></SPAN><STRONG> </STRONG></TH><TH nowrap="nowrap"><SPAN style="color: #363636;"><STRONG>Header</STRONG></SPAN><STRONG> </STRONG></TH><TH nowrap="nowrap"><SPAN style="color: #363636;"><STRONG>Detail</STRONG></SPAN><STRONG> </STRONG></TH><TH nowrap="nowrap"><SPAN style="color: #363636;"><STRONG>Value</STRONG></SPAN><STRONG> </STRONG></TH></TR><TR bgcolor="#ffffff"><TD bgcolor="#ffffff"><SPAN style="color: #363636;">1</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">HA</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">HA</SPAN></TD><TD bgcolor="#ffffff"> </TD></TR><TR bgcolor="#ffffff"><TD bgcolor="#ffffff"><SPAN style="color: #363636;">2</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">HA</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">a</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">1</SPAN> </TD></TR><TR bgcolor="#ffffff"><TD bgcolor="#ffffff"><SPAN style="color: #363636;">3</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">HA</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">b</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">2</SPAN> </TD></TR><TR bgcolor="#ffffff"><TD bgcolor="#ffffff"><SPAN style="color: #363636;">4</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">HA</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">c</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">3</SPAN> </TD></TR><TR bgcolor="#ffffff"><TD bgcolor="#ffffff"><SPAN style="color: #363636;">5</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">HA</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">d</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">4</SPAN> </TD></TR><TR bgcolor="#ffffff"><TD bgcolor="#ffffff"><SPAN style="color: #363636;">6</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">HB</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">HB</SPAN></TD><TD bgcolor="#ffffff"> </TD></TR><TR bgcolor="#ffffff"><TD bgcolor="#ffffff"><SPAN style="color: #363636;">7</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">HB</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">e</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">5</SPAN> </TD></TR><TR bgcolor="#ffffff"><TD bgcolor="#ffffff"><SPAN style="color: #363636;">8</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">HC</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">HC</SPAN></TD><TD bgcolor="#ffffff"> </TD></TR><TR bgcolor="#ffffff"><TD bgcolor="#ffffff"><SPAN style="color: #363636;">9</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">HC</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">f</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">6</SPAN> </TD></TR><TR bgcolor="#ffffff"><TD bgcolor="#ffffff"><SPAN style="color: #363636;">10</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">HC</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">g</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">7</SPAN> </TD></TR><TR bgcolor="#ffffff"><TD bgcolor="#ffffff"><SPAN style="color: #363636;">11</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">HC</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">h</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">8</SPAN> </TD></TR><TR bgcolor="#ffffff"><TD bgcolor="#ffffff"><SPAN style="color: #363636;">12</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">HC</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">i</SPAN></TD><TD bgcolor="#ffffff"><SPAN style="color: #363636;">9</SPAN></TD></TR></TBODY></TABLE><P></P><P>I thought it would be easy but have completely failed.</P><P></P><P>Any suggestions ?</P><P></P><P>****************************************************************</P><P></P><P>Here is an inline load to create the input data</P><P></P><P>InputData :</P><P>LOAD * INLINE [</P><P>&nbsp;&nbsp;&nbsp; Row, Description, Type, Value</P><P>&nbsp;&nbsp;&nbsp; 1, HA, Header</P><P>&nbsp;&nbsp;&nbsp; 2, a, Detail, 1</P><P>&nbsp;&nbsp;&nbsp; 3, b, Detail, 2</P><P>&nbsp;&nbsp;&nbsp; 4, c, Detail, 3</P><P>&nbsp;&nbsp;&nbsp; 5, d, Detail, 4</P><P>&nbsp;&nbsp;&nbsp; 6, HB, Header</P><P>&nbsp;&nbsp;&nbsp; 7, e, Detail, 5</P><P>&nbsp;&nbsp;&nbsp; 8, HC, Header</P><P>&nbsp;&nbsp;&nbsp; 9, f, Detail, 6</P><P>&nbsp;&nbsp;&nbsp; 10, g, Detail, 7</P><P>&nbsp;&nbsp;&nbsp; 11, h, Detail, 8</P><P>&nbsp;&nbsp;&nbsp; 12, i, Detail, 9</P><P>];</P></BODY></HTML> Sun, 23 Nov 2014 21:58:05 GMT https://community.qlik.com/t5/QlikView/Problem-Transforming-Data/m-p/802648#M668526 Anonymous 2014-11-23T21:58:05Z Re: Problem Transforming Data https://community.qlik.com/t5/QlikView/Problem-Transforming-Data/m-p/802649#M668527 <HTML><HEAD></HEAD><BODY><P>Hi,</P><P></P><P>one solution could be:</P><P></P><P><IMG alt="QlikCommunity_Thread_143238_Pic1.JPG.jpg" class="jive-image" src="https://community.qlik.com/legacyfs/online/71903_QlikCommunity_Thread_143238_Pic1.JPG.jpg" /></P><P></P><PRE __default_attr="plain" __jive_macro_name="code" class="jive_text_macro _jivemacro_uid_14167831680825807 jive_macro_code" jivemacro_uid="_14167831680825807"> <P>LOAD Row,</P> <P>&nbsp;&nbsp;&nbsp; If(Type='Header', Description, Peek(Header)) as Header,</P> <P>&nbsp;&nbsp;&nbsp; Description as Detail,</P> <P>&nbsp;&nbsp;&nbsp; Value</P> <P><SPAN>FROM [</SPAN><A class="jive-link-thread-small" data-containerid="2061" data-containertype="14" data-objectid="143238" data-objecttype="1" href="http://community.qlik.com/thread/143238">http://community.qlik.com/thread/143238</A><SPAN>] (html, codepage is 1252, embedded labels, table is @1);</SPAN></P> </PRE><P></P><P>hope this helps</P><P></P><P>regards</P><P></P><P>Marco</P></BODY></HTML> Sun, 23 Nov 2014 22:53:11 GMT https://community.qlik.com/t5/QlikView/Problem-Transforming-Data/m-p/802649#M668527 MarcoWedel 2014-11-23T22:53:11Z Re: Problem Transforming Data https://community.qlik.com/t5/QlikView/Problem-Transforming-Data/m-p/802650#M668528 <HTML><HEAD></HEAD><BODY><P><STRONG>Marco</STRONG></P><P></P><P>Many thanks, that works perfectly.</P><P></P><P></P><P><STRONG>Bill</STRONG></P></BODY></HTML> Sun, 23 Nov 2014 23:14:14 GMT https://community.qlik.com/t5/QlikView/Problem-Transforming-Data/m-p/802650#M668528 Anonymous 2014-11-23T23:14:14Z Re: Problem Transforming Data https://community.qlik.com/t5/QlikView/Problem-Transforming-Data/m-p/802651#M668529 <HTML><HEAD></HEAD><BODY><P>You're welcome</P><P></P><P>regards</P><P></P><P>Marco</P></BODY></HTML> Sun, 23 Nov 2014 23:33:33 GMT https://community.qlik.com/t5/QlikView/Problem-Transforming-Data/m-p/802651#M668529 MarcoWedel 2014-11-23T23:33:33Z