topic Re: Join 2 tables or....? in QlikView https://community.qlik.com/t5/QlikView/Join-2-tables-or/m-p/319961#M711041 <HTML><HEAD></HEAD><BODY><P>Hi,</P><P></P><P>&nbsp;&nbsp;&nbsp; Have a look at this Example.</P><P></P><P>&nbsp;&nbsp;&nbsp; I hope this will solve your problem.</P><P></P><P>Regards,</P><P>Kaushik Solanki</P></BODY></HTML> Fri, 24 Jun 2011 17:10:57 GMT kaushiknsolanki 2011-06-24T17:10:57Z Join 2 tables or....? https://community.qlik.com/t5/QlikView/Join-2-tables-or/m-p/319960#M711040 <HTML><HEAD></HEAD><BODY><P>QV10 SR2</P><P></P><P>I have the following 2 tables (from a 3rd party source):</P><P></P><P>Tab1:</P><P><STRONG>Question&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Answer&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Choice</STRONG></P><P>Job Function&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; District Nurse&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 01</P><P>Job Function&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Practice Nurse&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 02</P><P>Job Function&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Specialist Nurse&nbsp;&nbsp;&nbsp;&nbsp; 03</P><P>Job Function&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Clinical Nurse&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 04</P><P>Job Function&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Health Visitor&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 05</P><P>Specialist Areas&nbsp;&nbsp; Oncology&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 01</P><P>Specialist Areas&nbsp;&nbsp; Diabetes&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 02</P><P>Specialist Areas&nbsp;&nbsp; Wound Care&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 03</P><P>Specialist Areas&nbsp;&nbsp; A &amp; E&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 04</P><P>Specialist Areas&nbsp;&nbsp; Travel Health&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 05</P><P>Industry Sector&nbsp;&nbsp;&nbsp;&nbsp; Primary Care&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 01</P><P>Industry Sector&nbsp;&nbsp;&nbsp;&nbsp; Secondary Care&nbsp;&nbsp;&nbsp;&nbsp; 02</P><P>Industry Sector&nbsp;&nbsp;&nbsp;&nbsp; Private Sector&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 03</P><P></P><P></P><P>Tab2:</P><P><STRONG>CustomerID&nbsp;&nbsp;&nbsp; Question&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Choice</STRONG></P><P>1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Job Function&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 01</P><P>1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Specialist Areas&nbsp;&nbsp;&nbsp;&nbsp; 02</P><P>1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Industry Sector&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 01</P><P>2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Job Function&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 05</P><P>2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Specialist Areas&nbsp;&nbsp;&nbsp;&nbsp; 03</P><P>3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Job Function&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 02</P><P>3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Specialist Areas&nbsp;&nbsp;&nbsp;&nbsp; 01</P><P>3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Industry Sector&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 03</P><P></P><P>Here we can see that CustomerID 2 did <SPAN style="text-decoration: underline;">not</SPAN> answer the Industry Sector Question hence no record in table 2 for that question.</P><P></P><P>I need to be able to display all the customers and their questions and answers but also include Questions they didn't answer, so for example i need to display something like this...</P><P></P><P><STRONG>CustomerID&nbsp;&nbsp;&nbsp; Question&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Answer</STRONG></P><P>1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Job Function&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; District Nurse</P><P>1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Specialist Areas&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Diabetes</P><P>1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Industry Sector&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Primary Care</P><P>2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Job Function&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Health Visitor</P><P>2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Specialist Areas&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Wound Care</P><P>2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Industry Sector&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;Not Answered&gt;</P><P>3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Job Function&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Practice Nurse</P><P>3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Specialist Areas&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Oncology</P><P>3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Industry Sector&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Private Sector</P><P></P><P>This shows CustomerID 2 with an Industry Sector of &lt;Not Answered&gt; so the user will be able to select &lt;Not Answered&gt; and see which customers have missed which questions.</P><P></P><P></P><P>I have been trying to use some kind of Join in my script but to no avail</P><P></P><P>Any ideas about how best to tackle this would be much appreciated.</P></BODY></HTML> Fri, 24 Jun 2011 13:48:16 GMT https://community.qlik.com/t5/QlikView/Join-2-tables-or/m-p/319960#M711040 haymarketpaul 2011-06-24T13:48:16Z Re: Join 2 tables or....? https://community.qlik.com/t5/QlikView/Join-2-tables-or/m-p/319961#M711041 <HTML><HEAD></HEAD><BODY><P>Hi,</P><P></P><P>&nbsp;&nbsp;&nbsp; Have a look at this Example.</P><P></P><P>&nbsp;&nbsp;&nbsp; I hope this will solve your problem.</P><P></P><P>Regards,</P><P>Kaushik Solanki</P></BODY></HTML> Fri, 24 Jun 2011 17:10:57 GMT https://community.qlik.com/t5/QlikView/Join-2-tables-or/m-p/319961#M711041 kaushiknsolanki 2011-06-24T17:10:57Z Re: Join 2 tables or....? https://community.qlik.com/t5/QlikView/Join-2-tables-or/m-p/319962#M711042 <HTML><HEAD></HEAD><BODY><P>Here are three different and simpler approaches.&nbsp; I suspect there's a simpler way still, which is why I have so many attempts, but I'm giving up for now.&nbsp; My favorite so far is the second:</P><P></P><P><SPAN style="font-family: courier new,courier;">LEFT JOIN (Tab2)</SPAN><BR /><SPAN style="font-family: courier new,courier;">LOAD *</SPAN><BR /><SPAN style="font-family: courier new,courier;">RESIDENT Tab1</SPAN><BR /><SPAN style="font-family: courier new,courier;">;</SPAN><BR /><SPAN style="font-family: courier new,courier;">All:</SPAN><BR /><SPAN style="font-family: courier new,courier;">LOAD num(fieldvalue('CustomerID',recno())) as CustomerID</SPAN><BR /><SPAN style="font-family: courier new,courier;">AUTOGENERATE fieldvaluecount('CustomerID')</SPAN><BR /><SPAN style="font-family: courier new,courier;">;</SPAN><BR /><SPAN style="font-family: courier new,courier;">LEFT JOIN (All)</SPAN><BR /><SPAN style="font-family: courier new,courier;">LOAD text(fieldvalue('Question',recno())) as Question</SPAN><BR /><SPAN style="font-family: courier new,courier;">AUTOGENERATE fieldvaluecount('Question')</SPAN><BR /><SPAN style="font-family: courier new,courier;">;</SPAN><BR /><SPAN style="font-family: courier new,courier;">OUTER JOIN (Tab2)</SPAN><BR /><SPAN style="font-family: courier new,courier;">LOAD *</SPAN><BR /><SPAN style="font-family: courier new,courier;">RESIDENT All</SPAN><BR /><SPAN style="font-family: courier new,courier;">;</SPAN><BR /><SPAN style="font-family: courier new,courier;">Tab3:</SPAN><BR /><SPAN style="font-family: courier new,courier;">NOCONCATENATE</SPAN><BR /><SPAN style="font-family: courier new,courier;">LOAD </SPAN><BR /><SPAN style="font-family: courier new,courier;">CustomerID</SPAN><BR /><SPAN style="font-family: courier new,courier;">,Question</SPAN><BR /><SPAN style="font-family: courier new,courier;">,alt(Choice,00) as Choice</SPAN><BR /><SPAN style="font-family: courier new,courier;">,if(len(Answer),Answer,'&lt;Not Answered&gt;') as Answer</SPAN><BR /><SPAN style="font-family: courier new,courier;">RESIDENT Tab2</SPAN><BR /><SPAN style="font-family: courier new,courier;">;</SPAN><BR /><SPAN style="font-family: courier new,courier;">DROP TABLES All, Tab1, Tab2</SPAN><BR /><SPAN style="font-family: courier new,courier;">;</SPAN></P></BODY></HTML> Fri, 24 Jun 2011 19:51:02 GMT https://community.qlik.com/t5/QlikView/Join-2-tables-or/m-p/319962#M711042 johnw 2011-06-24T19:51:02Z Join 2 tables or....? https://community.qlik.com/t5/QlikView/Join-2-tables-or/m-p/319963#M711043 <HTML><HEAD></HEAD><BODY><P>That's fantastic guys - thankyou very much.</P><P></P><P>Going to spend today looking at how they work</P></BODY></HTML> Mon, 27 Jun 2011 09:29:53 GMT https://community.qlik.com/t5/QlikView/Join-2-tables-or/m-p/319963#M711043 haymarketpaul 2011-06-27T09:29:53Z