topic Re: Is it possible to transpose certain columns from an excel file in QV script to wanted format in QlikView https://community.qlik.com/t5/QlikView/Is-it-possible-to-transpose-certain-columns-from-an-excel-file/m-p/1462349#M792561 <HTML><HEAD></HEAD><BODY><P>Hi Johan,</P><P></P><P>Try this.</P><P></P><P><SPAN style="font-size: 9.0pt; font-family: 'Courier New'; color: black;"><SPAN style="font-size: 10pt;">Data:</SPAN><BR /> </SPAN><SPAN style="font-size: 10pt;"><STRONG style="color: blue; font-family: 'Courier New';">LOAD</STRONG> <SPAN style="font-family: 'Courier New'; color: maroon;">SalesRegDate</SPAN><SPAN style="font-family: 'Courier New'; color: black;">, <BR /> </SPAN><SPAN style="font-family: 'Courier New'; color: maroon;">Campaign</SPAN><SPAN style="font-family: 'Courier New'; color: black;">, <BR /> </SPAN><SPAN style="font-family: 'Courier New'; color: maroon;">Promo</SPAN><SPAN style="font-family: 'Courier New'; color: black;">, <BR /> </SPAN><SPAN style="font-family: 'Courier New'; color: maroon;">StoreOrgNr1</SPAN><SPAN style="font-family: 'Courier New'; color: black;">, <BR /> </SPAN><SPAN style="font-family: 'Courier New'; color: maroon;">StoreOrgNr2</SPAN><SPAN style="font-family: 'Courier New'; color: black;">, <BR /> </SPAN><SPAN style="font-family: 'Courier New'; color: maroon;">StoreOrgNr3</SPAN><SPAN style="font-family: 'Courier New'; color: black;">, <BR /> </SPAN><SPAN style="font-family: 'Courier New'; color: maroon;">StoreOrgNr4</SPAN><SPAN style="font-family: 'Courier New'; color: black;">, <BR /> </SPAN><SPAN style="font-family: 'Courier New'; color: maroon;">StoreOrgNr5</SPAN><SPAN style="font-family: 'Courier New'; color: black;">, <BR /> </SPAN><SPAN style="font-family: 'Courier New'; color: maroon;">Pcode1</SPAN> <SPAN style="font-family: 'Courier New'; color: blue;">as</SPAN> <SPAN style="font-family: 'Courier New'; color: maroon;">Pcode</SPAN><SPAN style="font-family: 'Courier New'; color: black;">, <BR /> </SPAN><SPAN style="font-family: 'Courier New'; color: maroon;">Tcode1</SPAN> <SPAN style="font-family: 'Courier New'; color: blue;">as</SPAN> <SPAN style="font-family: 'Courier New'; color: maroon;">Tcode</SPAN><SPAN style="font-family: 'Courier New'; color: black;"><BR /> </SPAN><SPAN style="font-family: 'Courier New'; color: blue;">FROM</SPAN><SPAN style="font-family: 'Courier New'; color: black;"><BR /> </SPAN><SPAN style="font-family: 'Courier New'; color: maroon;">[..\..\Downloads\QlikViewHelpCrossTableTranspose.xlsx]</SPAN><SPAN style="font-family: 'Courier New'; color: black;"><BR /> (</SPAN><SPAN style="font-family: 'Courier New'; color: blue;">ooxml</SPAN><SPAN style="font-family: 'Courier New'; color: black;">, </SPAN><SPAN style="font-family: 'Courier New'; color: blue;">embedded</SPAN> <SPAN style="font-family: 'Courier New'; color: blue;">labels</SPAN><SPAN style="font-family: 'Courier New'; color: black;">, </SPAN><SPAN style="font-family: 'Courier New'; color: blue;">table</SPAN> <SPAN style="font-family: 'Courier New'; color: blue;">is</SPAN><SPAN style="font-family: 'Courier New'; color: black;"> InsertData);<BR /> <BR /> </SPAN><SPAN style="font-family: 'Courier New'; color: blue;">Concatenate</SPAN><SPAN style="font-family: 'Courier New'; color: black;"> (Data)<BR /> <BR /> </SPAN><STRONG style="color: blue; font-family: 'Courier New';">LOAD</STRONG> <SPAN style="font-family: 'Courier New'; color: maroon;">SalesRegDate</SPAN><SPAN style="font-family: 'Courier New'; color: black;">, <BR /> </SPAN><SPAN style="font-family: 'Courier New'; color: maroon;">Campaign</SPAN><SPAN style="font-family: 'Courier New'; color: black;">, <BR /> </SPAN><SPAN style="font-family: 'Courier New'; color: maroon;">Promo</SPAN><SPAN style="font-family: 'Courier New'; color: black;">, <BR /> </SPAN><SPAN style="font-family: 'Courier New'; color: maroon;">StoreOrgNr1</SPAN><SPAN style="font-family: 'Courier New'; color: black;">, <BR /> </SPAN><SPAN style="font-family: 'Courier New'; color: maroon;">StoreOrgNr2</SPAN><SPAN style="font-family: 'Courier New'; color: black;">, <BR /> </SPAN><SPAN style="font-family: 'Courier New'; color: maroon;">StoreOrgNr3</SPAN><SPAN style="font-family: 'Courier New'; color: black;">, <BR /> </SPAN><SPAN style="font-family: 'Courier New'; color: maroon;">StoreOrgNr4</SPAN><SPAN style="font-family: 'Courier New'; color: black;">, <BR /> </SPAN><SPAN style="font-family: 'Courier New'; color: maroon;">StoreOrgNr5</SPAN><SPAN style="font-family: 'Courier New'; color: black;">, <BR /> </SPAN><SPAN style="font-family: 'Courier New'; color: maroon;">Pcode1</SPAN> <SPAN style="font-family: 'Courier New'; color: blue;">as</SPAN> <SPAN style="font-family: 'Courier New'; color: maroon;">Pcode</SPAN><SPAN style="font-family: 'Courier New'; color: black;">,&nbsp; <BR /> </SPAN><SPAN style="font-family: 'Courier New'; color: maroon;">Tcode2</SPAN> <SPAN style="font-family: 'Courier New'; color: blue;">as</SPAN> <SPAN style="font-family: 'Courier New'; color: maroon;">Tcode</SPAN><SPAN style="font-family: 'Courier New'; color: black;"><BR /> </SPAN><SPAN style="font-family: 'Courier New'; color: blue;">FROM</SPAN><SPAN style="font-family: 'Courier New'; color: black;"><BR /> </SPAN><SPAN style="font-family: 'Courier New'; color: maroon;">[..\..\Downloads\QlikViewHelpCrossTableTranspose.xlsx]</SPAN><SPAN style="font-family: 'Courier New'; color: black;"><BR /> (</SPAN><SPAN style="font-family: 'Courier New'; color: blue;">ooxml</SPAN><SPAN style="font-family: 'Courier New'; color: black;">, </SPAN><SPAN style="font-family: 'Courier New'; color: blue;">embedded</SPAN> <SPAN style="font-family: 'Courier New'; color: blue;">labels</SPAN><SPAN style="font-family: 'Courier New'; color: black;">, </SPAN><SPAN style="font-family: 'Courier New'; color: blue;">table</SPAN> <SPAN style="font-family: 'Courier New'; color: blue;">is</SPAN><SPAN style="font-family: 'Courier New'; color: black;"> InsertData) </SPAN><SPAN style="font-family: 'Courier New'; color: blue;">where</SPAN> <SPAN style="font-family: 'Courier New'; color: blue;">Len</SPAN><SPAN style="font-family: 'Courier New'; color: black;">(</SPAN><SPAN style="font-family: 'Courier New'; color: blue;">Trim</SPAN><SPAN style="font-family: 'Courier New'; color: black;">(</SPAN><SPAN style="font-family: 'Courier New'; color: maroon;">Tcode2</SPAN><SPAN style="font-family: 'Courier New'; color: black;">))&gt;0;<BR /><BR /> Final:<BR /> </SPAN><SPAN style="font-family: 'Courier New'; color: blue;">CrossTable</SPAN><SPAN style="font-family: 'Courier New'; color: black;">(</SPAN><SPAN style="font-family: 'Courier New'; color: maroon;">ColumnName</SPAN><SPAN style="font-family: 'Courier New'; color: black;">, </SPAN><SPAN style="font-family: 'Courier New'; color: maroon;">StoreOrgNr</SPAN><SPAN style="font-family: 'Courier New'; color: black;">, 5)<BR /> </SPAN><STRONG style="color: blue; font-family: 'Courier New';">LOAD</STRONG> <SPAN style="font-family: 'Courier New'; color: maroon;">SalesRegDate</SPAN><SPAN style="font-family: 'Courier New'; color: black;">, <BR /> </SPAN><SPAN style="font-family: 'Courier New'; color: maroon;">Campaign</SPAN><SPAN style="font-family: 'Courier New'; color: black;">, <BR /> </SPAN><SPAN style="font-family: 'Courier New'; color: maroon;">Promo</SPAN><SPAN style="font-family: 'Courier New'; color: black;">, <BR /> </SPAN><SPAN style="font-family: 'Courier New'; color: maroon;">Pcode</SPAN><SPAN style="font-family: 'Courier New'; color: black;">, <BR /> </SPAN><SPAN style="font-family: 'Courier New'; color: maroon;">Tcode</SPAN><SPAN style="font-family: 'Courier New'; color: black;">, <BR /> </SPAN><SPAN style="font-family: 'Courier New'; color: maroon;">StoreOrgNr1</SPAN><SPAN style="font-family: 'Courier New'; color: black;">, <BR /> </SPAN><SPAN style="font-family: 'Courier New'; color: maroon;">StoreOrgNr2</SPAN><SPAN style="font-family: 'Courier New'; color: black;">, <BR /> </SPAN><SPAN style="font-family: 'Courier New'; color: maroon;">StoreOrgNr3</SPAN><SPAN style="font-family: 'Courier New'; color: black;">, <BR /> </SPAN><SPAN style="font-family: 'Courier New'; color: maroon;">StoreOrgNr4</SPAN><SPAN style="font-family: 'Courier New'; color: black;">, <BR /> </SPAN><SPAN style="font-family: 'Courier New'; color: maroon;">StoreOrgNr5</SPAN><SPAN style="font-family: 'Courier New'; color: black;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <BR /> </SPAN><SPAN style="font-family: 'Courier New'; color: blue;">Resident</SPAN><SPAN style="font-family: 'Courier New'; color: black;"> Data;<BR /> <BR /> </SPAN><STRONG style="color: blue; font-family: 'Courier New';">DROP</STRONG> <SPAN style="font-family: 'Courier New'; color: blue;">Table</SPAN></SPAN><SPAN style="font-size: 9.0pt; font-family: 'Courier New'; color: black;"><SPAN style="font-size: 10pt;"> Data;</SPAN> </SPAN></P></BODY></HTML> Thu, 11 Jan 2018 05:24:49 GMT tamilarasu 2018-01-11T05:24:49Z Is it possible to transpose certain columns from an excel file in QV script to wanted format https://community.qlik.com/t5/QlikView/Is-it-possible-to-transpose-certain-columns-from-an-excel-file/m-p/1462347#M792559 <HTML><HEAD></HEAD><BODY><P>Hi,</P><P></P><P>I'm really struggling with creating a table in QlikView script to a certain format. Please view attached Excel-file:</P><P></P><P>"InsertData" tab displays how structure is today</P><P></P><P>"WantedFormat" tab displays what format I wish to get.</P><P></P><P>If anyone can help me it would me much appreciated.</P><P></P><P></P><P>Br,</P><P>Johan</P></BODY></HTML> Wed, 25 Nov 2020 16:16:04 GMT https://community.qlik.com/t5/QlikView/Is-it-possible-to-transpose-certain-columns-from-an-excel-file/m-p/1462347#M792559 __johan__ 2020-11-25T16:16:04Z Re: Is it possible to transpose certain columns from an excel file in QV script to wanted format https://community.qlik.com/t5/QlikView/Is-it-possible-to-transpose-certain-columns-from-an-excel-file/m-p/1462348#M792560 <HTML><HEAD></HEAD><BODY><P>Hi Johan,</P><P></P><P>Try if this helps:</P><P></P><P><SPAN style="font-size: 8pt;">Data1:<BR /> </SPAN><SPAN style="color: #0000ff; font-size: 8pt;">CrossTable</SPAN><SPAN style="font-size: 8pt;">(</SPAN><SPAN style="color: #800000; font-size: 8pt;">DummyCol1</SPAN><SPAN style="font-size: 8pt;">, </SPAN><SPAN style="color: #800000; font-size: 8pt;">StoreOrgNr</SPAN><SPAN style="font-size: 8pt;">, 3)<BR /> </SPAN><STRONG style=": ; color: #0000ff; font-size: 8pt;">LOAD</STRONG><SPAN style="font-size: 8pt;"> </SPAN><SPAN style="color: #800000; font-size: 8pt;">SalesRegDate</SPAN><SPAN style="font-size: 8pt;">, <BR /> </SPAN><SPAN style="color: #800000; font-size: 8pt;">Campaign</SPAN><SPAN style="font-size: 8pt;">, <BR /> </SPAN><SPAN style="color: #800000; font-size: 8pt;">Promo</SPAN><SPAN style="font-size: 8pt;">, <BR /> </SPAN><SPAN style="color: #800000; font-size: 8pt;">StoreOrgNr1</SPAN><SPAN style="font-size: 8pt;">, <BR /> </SPAN><SPAN style="color: #800000; font-size: 8pt;">StoreOrgNr2</SPAN><SPAN style="font-size: 8pt;">, <BR /> </SPAN><SPAN style="color: #800000; font-size: 8pt;">StoreOrgNr3</SPAN><SPAN style="font-size: 8pt;">, <BR /> </SPAN><SPAN style="color: #800000; font-size: 8pt;">StoreOrgNr4</SPAN><SPAN style="font-size: 8pt;">, <BR /> </SPAN><SPAN style="color: #800000; font-size: 8pt;">StoreOrgNr5</SPAN><SPAN style="font-size: 8pt;"><BR /> </SPAN><SPAN style="color: #0000ff; font-size: 8pt;">FROM</SPAN><SPAN style="font-size: 8pt;"><BR /> </SPAN><SPAN style="color: #800000; font-size: 8pt;">[&lt;&lt;Path&gt;&gt;\Testing_Excel.xlsx]</SPAN><SPAN style="font-size: 8pt;"><BR /> (</SPAN><SPAN style="color: #0000ff; font-size: 8pt;">ooxml</SPAN><SPAN style="font-size: 8pt;">, </SPAN><SPAN style="color: #0000ff; font-size: 8pt;">embedded</SPAN><SPAN style="font-size: 8pt;"> </SPAN><SPAN style="color: #0000ff; font-size: 8pt;">labels</SPAN><SPAN style="font-size: 8pt;">, </SPAN><SPAN style="color: #0000ff; font-size: 8pt;">table</SPAN><SPAN style="font-size: 8pt;"> </SPAN><SPAN style="color: #0000ff; font-size: 8pt;">is</SPAN><SPAN style="font-size: 8pt;"> InsertData);<BR /> <BR /> Data2:<BR /> </SPAN><SPAN style="color: #0000ff; font-size: 8pt;">CrossTable</SPAN><SPAN style="font-size: 8pt;">(</SPAN><SPAN style="color: #800000; font-size: 8pt;">DummyCol2</SPAN><SPAN style="font-size: 8pt;">, </SPAN><SPAN style="color: #800000; font-size: 8pt;">PCode</SPAN><SPAN style="font-size: 8pt;">, 3)<BR /> </SPAN><STRONG style=": ; color: #0000ff; font-size: 8pt;">LOAD</STRONG><SPAN style="font-size: 8pt;"> </SPAN><SPAN style="color: #800000; font-size: 8pt;">SalesRegDate</SPAN><SPAN style="font-size: 8pt;">, <BR /> </SPAN><SPAN style="color: #800000; font-size: 8pt;">Campaign</SPAN><SPAN style="font-size: 8pt;">, <BR /> </SPAN><SPAN style="color: #800000; font-size: 8pt;">Promo</SPAN><SPAN style="font-size: 8pt;">, <BR /> </SPAN><SPAN style="color: #800000; font-size: 8pt;">Pcode1</SPAN><SPAN style="font-size: 8pt;"><BR /> </SPAN><SPAN style="color: #0000ff; font-size: 8pt;">FROM</SPAN><SPAN style="font-size: 8pt;"><BR /> </SPAN><SPAN style="color: #800000; font-size: 8pt;">[&lt;&lt;Path&gt;&gt;\Testing_Excel.xlsx]</SPAN><SPAN style="font-size: 8pt;"><BR /> (</SPAN><SPAN style="color: #0000ff; font-size: 8pt;">ooxml</SPAN><SPAN style="font-size: 8pt;">, </SPAN><SPAN style="color: #0000ff; font-size: 8pt;">embedded</SPAN><SPAN style="font-size: 8pt;"> </SPAN><SPAN style="color: #0000ff; font-size: 8pt;">labels</SPAN><SPAN style="font-size: 8pt;">, </SPAN><SPAN style="color: #0000ff; font-size: 8pt;">table</SPAN><SPAN style="font-size: 8pt;"> </SPAN><SPAN style="color: #0000ff; font-size: 8pt;">is</SPAN><SPAN style="font-size: 8pt;"> InsertData);<BR /> <BR /> Data3:<BR /> </SPAN><SPAN style="color: #0000ff; font-size: 8pt;">CrossTable</SPAN><SPAN style="font-size: 8pt;">(</SPAN><SPAN style="color: #800000; font-size: 8pt;">DummyCol3</SPAN><SPAN style="font-size: 8pt;">, </SPAN><SPAN style="color: #800000; font-size: 8pt;">TCode</SPAN><SPAN style="font-size: 8pt;">, 3)<BR /> </SPAN><STRONG style=": ; color: #0000ff; font-size: 8pt;">LOAD</STRONG><SPAN style="font-size: 8pt;"> </SPAN><SPAN style="color: #800000; font-size: 8pt;">SalesRegDate</SPAN><SPAN style="font-size: 8pt;">, <BR /> </SPAN><SPAN style="color: #800000; font-size: 8pt;">Campaign</SPAN><SPAN style="font-size: 8pt;">, <BR /> </SPAN><SPAN style="color: #800000; font-size: 8pt;">Promo</SPAN><SPAN style="font-size: 8pt;">, <BR /> </SPAN><SPAN style="color: #800000; font-size: 8pt;">Tcode1</SPAN><SPAN style="font-size: 8pt;">, <BR /> </SPAN><SPAN style="color: #800000; font-size: 8pt;">Tcode2</SPAN><SPAN style="font-size: 8pt;"><BR /> </SPAN><SPAN style="color: #0000ff; font-size: 8pt;">FROM</SPAN><SPAN style="font-size: 8pt;"><BR /> </SPAN><SPAN style="color: #800000; font-size: 8pt;">[&lt;&lt;Path&gt;&gt;\Testing_Excel.xlsx]</SPAN><SPAN style="font-size: 8pt;"><BR /> (</SPAN><SPAN style="color: #0000ff; font-size: 8pt;">ooxml</SPAN><SPAN style="font-size: 8pt;">, </SPAN><SPAN style="color: #0000ff; font-size: 8pt;">embedded</SPAN><SPAN style="font-size: 8pt;"> </SPAN><SPAN style="color: #0000ff; font-size: 8pt;">labels</SPAN><SPAN style="font-size: 8pt;">, </SPAN><SPAN style="color: #0000ff; font-size: 8pt;">table</SPAN><SPAN style="font-size: 8pt;"> </SPAN><SPAN style="color: #0000ff; font-size: 8pt;">is</SPAN><SPAN style="font-size: 8pt;"> InsertData);<BR /> <BR /> </SPAN><SPAN style="color: #0000ff; font-size: 8pt;">left</SPAN><SPAN style="font-size: 8pt;"> </SPAN><SPAN style="color: #0000ff; font-size: 8pt;">join</SPAN><SPAN style="font-size: 8pt;"> (Data1) </SPAN><STRONG style=": ; color: #0000ff; font-size: 8pt;">load</STRONG><SPAN style="font-size: 8pt;"> * </SPAN><SPAN style="color: #0000ff; font-size: 8pt;">resident</SPAN><SPAN style="font-size: 8pt;"> Data2;<BR /> </SPAN><SPAN style="color: #0000ff; font-size: 8pt;">left</SPAN><SPAN style="font-size: 8pt;"> </SPAN><SPAN style="color: #0000ff; font-size: 8pt;">join</SPAN><SPAN style="font-size: 8pt;"> (Data1) </SPAN><STRONG style=": ; color: #0000ff; font-size: 8pt;">load</STRONG><SPAN style="font-size: 8pt;"> * </SPAN><SPAN style="color: #0000ff; font-size: 8pt;">resident</SPAN><SPAN style="font-size: 8pt;"> Data3;<BR /> <BR /> </SPAN><STRONG style=": ; color: #0000ff; font-size: 8pt;">drop</STRONG><SPAN style="font-size: 8pt;"> </SPAN><SPAN style="color: #0000ff; font-size: 8pt;">tables</SPAN><SPAN style="font-size: 8pt;"> Data2, Data3;<BR /> </SPAN><STRONG style=": ; color: #0000ff; font-size: 8pt;">drop</STRONG><SPAN style="font-size: 8pt;"> </SPAN><SPAN style="color: #0000ff; font-size: 8pt;">Fields</SPAN><SPAN style="font-size: 8pt;"> </SPAN><SPAN style="color: #800000; font-size: 8pt;">DummyCol1</SPAN><SPAN style="font-size: 8pt;">, </SPAN><SPAN style="color: #800000; font-size: 8pt;">DummyCol2</SPAN><SPAN style="font-size: 8pt;">, </SPAN><SPAN style="color: #800000; font-size: 8pt;">DummyCol3</SPAN><SPAN style="font-size: 8pt;">; </SPAN></P><P></P><P>Regards,</P><P>Abey</P></BODY></HTML> Thu, 11 Jan 2018 03:51:17 GMT https://community.qlik.com/t5/QlikView/Is-it-possible-to-transpose-certain-columns-from-an-excel-file/m-p/1462348#M792560 abeyphilip 2018-01-11T03:51:17Z Re: Is it possible to transpose certain columns from an excel file in QV script to wanted format https://community.qlik.com/t5/QlikView/Is-it-possible-to-transpose-certain-columns-from-an-excel-file/m-p/1462349#M792561 <HTML><HEAD></HEAD><BODY><P>Hi Johan,</P><P></P><P>Try this.</P><P></P><P><SPAN style="font-size: 9.0pt; font-family: 'Courier New'; color: black;"><SPAN style="font-size: 10pt;">Data:</SPAN><BR /> </SPAN><SPAN style="font-size: 10pt;"><STRONG style="color: blue; font-family: 'Courier New';">LOAD</STRONG> <SPAN style="font-family: 'Courier New'; color: maroon;">SalesRegDate</SPAN><SPAN style="font-family: 'Courier New'; color: black;">, <BR /> </SPAN><SPAN style="font-family: 'Courier New'; color: maroon;">Campaign</SPAN><SPAN style="font-family: 'Courier New'; color: black;">, <BR /> </SPAN><SPAN style="font-family: 'Courier New'; color: maroon;">Promo</SPAN><SPAN style="font-family: 'Courier New'; color: black;">, <BR /> </SPAN><SPAN style="font-family: 'Courier New'; color: maroon;">StoreOrgNr1</SPAN><SPAN style="font-family: 'Courier New'; color: black;">, <BR /> </SPAN><SPAN style="font-family: 'Courier New'; color: maroon;">StoreOrgNr2</SPAN><SPAN style="font-family: 'Courier New'; color: black;">, <BR /> </SPAN><SPAN style="font-family: 'Courier New'; color: maroon;">StoreOrgNr3</SPAN><SPAN style="font-family: 'Courier New'; color: black;">, <BR /> </SPAN><SPAN style="font-family: 'Courier New'; color: maroon;">StoreOrgNr4</SPAN><SPAN style="font-family: 'Courier New'; color: black;">, <BR /> </SPAN><SPAN style="font-family: 'Courier New'; color: maroon;">StoreOrgNr5</SPAN><SPAN style="font-family: 'Courier New'; color: black;">, <BR /> </SPAN><SPAN style="font-family: 'Courier New'; color: maroon;">Pcode1</SPAN> <SPAN style="font-family: 'Courier New'; color: blue;">as</SPAN> <SPAN style="font-family: 'Courier New'; color: maroon;">Pcode</SPAN><SPAN style="font-family: 'Courier New'; color: black;">, <BR /> </SPAN><SPAN style="font-family: 'Courier New'; color: maroon;">Tcode1</SPAN> <SPAN style="font-family: 'Courier New'; color: blue;">as</SPAN> <SPAN style="font-family: 'Courier New'; color: maroon;">Tcode</SPAN><SPAN style="font-family: 'Courier New'; color: black;"><BR /> </SPAN><SPAN style="font-family: 'Courier New'; color: blue;">FROM</SPAN><SPAN style="font-family: 'Courier New'; color: black;"><BR /> </SPAN><SPAN style="font-family: 'Courier New'; color: maroon;">[..\..\Downloads\QlikViewHelpCrossTableTranspose.xlsx]</SPAN><SPAN style="font-family: 'Courier New'; color: black;"><BR /> (</SPAN><SPAN style="font-family: 'Courier New'; color: blue;">ooxml</SPAN><SPAN style="font-family: 'Courier New'; color: black;">, </SPAN><SPAN style="font-family: 'Courier New'; color: blue;">embedded</SPAN> <SPAN style="font-family: 'Courier New'; color: blue;">labels</SPAN><SPAN style="font-family: 'Courier New'; color: black;">, </SPAN><SPAN style="font-family: 'Courier New'; color: blue;">table</SPAN> <SPAN style="font-family: 'Courier New'; color: blue;">is</SPAN><SPAN style="font-family: 'Courier New'; color: black;"> InsertData);<BR /> <BR /> </SPAN><SPAN style="font-family: 'Courier New'; color: blue;">Concatenate</SPAN><SPAN style="font-family: 'Courier New'; color: black;"> (Data)<BR /> <BR /> </SPAN><STRONG style="color: blue; font-family: 'Courier New';">LOAD</STRONG> <SPAN style="font-family: 'Courier New'; color: maroon;">SalesRegDate</SPAN><SPAN style="font-family: 'Courier New'; color: black;">, <BR /> </SPAN><SPAN style="font-family: 'Courier New'; color: maroon;">Campaign</SPAN><SPAN style="font-family: 'Courier New'; color: black;">, <BR /> </SPAN><SPAN style="font-family: 'Courier New'; color: maroon;">Promo</SPAN><SPAN style="font-family: 'Courier New'; color: black;">, <BR /> </SPAN><SPAN style="font-family: 'Courier New'; color: maroon;">StoreOrgNr1</SPAN><SPAN style="font-family: 'Courier New'; color: black;">, <BR /> </SPAN><SPAN style="font-family: 'Courier New'; color: maroon;">StoreOrgNr2</SPAN><SPAN style="font-family: 'Courier New'; color: black;">, <BR /> </SPAN><SPAN style="font-family: 'Courier New'; color: maroon;">StoreOrgNr3</SPAN><SPAN style="font-family: 'Courier New'; color: black;">, <BR /> </SPAN><SPAN style="font-family: 'Courier New'; color: maroon;">StoreOrgNr4</SPAN><SPAN style="font-family: 'Courier New'; color: black;">, <BR /> </SPAN><SPAN style="font-family: 'Courier New'; color: maroon;">StoreOrgNr5</SPAN><SPAN style="font-family: 'Courier New'; color: black;">, <BR /> </SPAN><SPAN style="font-family: 'Courier New'; color: maroon;">Pcode1</SPAN> <SPAN style="font-family: 'Courier New'; color: blue;">as</SPAN> <SPAN style="font-family: 'Courier New'; color: maroon;">Pcode</SPAN><SPAN style="font-family: 'Courier New'; color: black;">,&nbsp; <BR /> </SPAN><SPAN style="font-family: 'Courier New'; color: maroon;">Tcode2</SPAN> <SPAN style="font-family: 'Courier New'; color: blue;">as</SPAN> <SPAN style="font-family: 'Courier New'; color: maroon;">Tcode</SPAN><SPAN style="font-family: 'Courier New'; color: black;"><BR /> </SPAN><SPAN style="font-family: 'Courier New'; color: blue;">FROM</SPAN><SPAN style="font-family: 'Courier New'; color: black;"><BR /> </SPAN><SPAN style="font-family: 'Courier New'; color: maroon;">[..\..\Downloads\QlikViewHelpCrossTableTranspose.xlsx]</SPAN><SPAN style="font-family: 'Courier New'; color: black;"><BR /> (</SPAN><SPAN style="font-family: 'Courier New'; color: blue;">ooxml</SPAN><SPAN style="font-family: 'Courier New'; color: black;">, </SPAN><SPAN style="font-family: 'Courier New'; color: blue;">embedded</SPAN> <SPAN style="font-family: 'Courier New'; color: blue;">labels</SPAN><SPAN style="font-family: 'Courier New'; color: black;">, </SPAN><SPAN style="font-family: 'Courier New'; color: blue;">table</SPAN> <SPAN style="font-family: 'Courier New'; color: blue;">is</SPAN><SPAN style="font-family: 'Courier New'; color: black;"> InsertData) </SPAN><SPAN style="font-family: 'Courier New'; color: blue;">where</SPAN> <SPAN style="font-family: 'Courier New'; color: blue;">Len</SPAN><SPAN style="font-family: 'Courier New'; color: black;">(</SPAN><SPAN style="font-family: 'Courier New'; color: blue;">Trim</SPAN><SPAN style="font-family: 'Courier New'; color: black;">(</SPAN><SPAN style="font-family: 'Courier New'; color: maroon;">Tcode2</SPAN><SPAN style="font-family: 'Courier New'; color: black;">))&gt;0;<BR /><BR /> Final:<BR /> </SPAN><SPAN style="font-family: 'Courier New'; color: blue;">CrossTable</SPAN><SPAN style="font-family: 'Courier New'; color: black;">(</SPAN><SPAN style="font-family: 'Courier New'; color: maroon;">ColumnName</SPAN><SPAN style="font-family: 'Courier New'; color: black;">, </SPAN><SPAN style="font-family: 'Courier New'; color: maroon;">StoreOrgNr</SPAN><SPAN style="font-family: 'Courier New'; color: black;">, 5)<BR /> </SPAN><STRONG style="color: blue; font-family: 'Courier New';">LOAD</STRONG> <SPAN style="font-family: 'Courier New'; color: maroon;">SalesRegDate</SPAN><SPAN style="font-family: 'Courier New'; color: black;">, <BR /> </SPAN><SPAN style="font-family: 'Courier New'; color: maroon;">Campaign</SPAN><SPAN style="font-family: 'Courier New'; color: black;">, <BR /> </SPAN><SPAN style="font-family: 'Courier New'; color: maroon;">Promo</SPAN><SPAN style="font-family: 'Courier New'; color: black;">, <BR /> </SPAN><SPAN style="font-family: 'Courier New'; color: maroon;">Pcode</SPAN><SPAN style="font-family: 'Courier New'; color: black;">, <BR /> </SPAN><SPAN style="font-family: 'Courier New'; color: maroon;">Tcode</SPAN><SPAN style="font-family: 'Courier New'; color: black;">, <BR /> </SPAN><SPAN style="font-family: 'Courier New'; color: maroon;">StoreOrgNr1</SPAN><SPAN style="font-family: 'Courier New'; color: black;">, <BR /> </SPAN><SPAN style="font-family: 'Courier New'; color: maroon;">StoreOrgNr2</SPAN><SPAN style="font-family: 'Courier New'; color: black;">, <BR /> </SPAN><SPAN style="font-family: 'Courier New'; color: maroon;">StoreOrgNr3</SPAN><SPAN style="font-family: 'Courier New'; color: black;">, <BR /> </SPAN><SPAN style="font-family: 'Courier New'; color: maroon;">StoreOrgNr4</SPAN><SPAN style="font-family: 'Courier New'; color: black;">, <BR /> </SPAN><SPAN style="font-family: 'Courier New'; color: maroon;">StoreOrgNr5</SPAN><SPAN style="font-family: 'Courier New'; color: black;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <BR /> </SPAN><SPAN style="font-family: 'Courier New'; color: blue;">Resident</SPAN><SPAN style="font-family: 'Courier New'; color: black;"> Data;<BR /> <BR /> </SPAN><STRONG style="color: blue; font-family: 'Courier New';">DROP</STRONG> <SPAN style="font-family: 'Courier New'; color: blue;">Table</SPAN></SPAN><SPAN style="font-size: 9.0pt; font-family: 'Courier New'; color: black;"><SPAN style="font-size: 10pt;"> Data;</SPAN> </SPAN></P></BODY></HTML> Thu, 11 Jan 2018 05:24:49 GMT https://community.qlik.com/t5/QlikView/Is-it-possible-to-transpose-certain-columns-from-an-excel-file/m-p/1462349#M792561 tamilarasu 2018-01-11T05:24:49Z Re: Is it possible to transpose certain columns from an excel file in QV script to wanted format https://community.qlik.com/t5/QlikView/Is-it-possible-to-transpose-certain-columns-from-an-excel-file/m-p/1462350#M792562 <HTML><HEAD></HEAD><BODY><P>Thank you Abey! This worked really well</P></BODY></HTML> Thu, 11 Jan 2018 13:53:50 GMT https://community.qlik.com/t5/QlikView/Is-it-possible-to-transpose-certain-columns-from-an-excel-file/m-p/1462350#M792562 __johan__ 2018-01-11T13:53:50Z