topic Join distinctly with non-unique key in App Development https://community.qlik.com/t5/App-Development/Join-distinctly-with-non-unique-key/m-p/1814280#M66569 <P>Let's say I have the following table parts (of two much larger tables)...</P><P>CreditTable:</P><TABLE border="1"><TBODY><TR><TD>Project</TD><TD>Contract Date</TD><TD>Value</TD><TD>Line</TD><TD>Position</TD></TR><TR><TD>396</TD><TD>2/17/21</TD><TD>54</TD><TD>52</TD><TD>0</TD></TR><TR><TD>396</TD><TD>3/15/21</TD><TD>78</TD><TD>44</TD><TD>1</TD></TR><TR><TD>396</TD><TD>3/15/21</TD><TD>78</TD><TD>44</TD><TD>3</TD></TR><TR><TD>...</TD><TD>...</TD><TD>...</TD><TD>...</TD><TD>...</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>OrderTable:</P><TABLE border="1"><TBODY><TR><TD><P>Project</P></TD><TD>Contract Date</TD><TD>Value</TD><TD>Line</TD><TD>Position</TD></TR><TR><TD>396</TD><TD>2/17/21</TD><TD>54</TD><TD>30</TD><TD>4</TD></TR><TR><TD>396</TD><TD>3/15/21</TD><TD>78</TD><TD>22</TD><TD>1</TD></TR><TR><TD>396</TD><TD>3/15/21</TD><TD>78</TD><TD>22</TD><TD>2</TD></TR><TR><TD>...</TD><TD>...</TD><TD>...</TD><TD>...</TD><TD>...</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>I need to match a credit to an order by Project, Contract Date, and Value. I need it to be a one-to-one matching. When I run my current script...</P><P>JoinedTable:</P><P>LOAD DISTINCT</P><P>Project&amp;ContractDate&amp;Value&nbsp; &nbsp;AS CreditKey</P><P>Project&amp;'-'&amp;Line&amp;'-'&amp;Position&nbsp; AS OrderNumber</P><P>RESIDENT OrderTable;</P><P>LEFT JOIN LOAD</P><P>Project&amp;ContractDate&amp;Value&nbsp; &nbsp;AS CreditKey</P><P>Project&amp;'-'&amp;Line&amp;'-'&amp;Position&nbsp; AS CreditNumber</P><P>RESIDENT CreditTable;</P><P>&nbsp;</P><P>I get something like this...</P><TABLE border="1"><TBODY><TR><TD>CreditKey</TD><TD>OrderNumber</TD><TD>CreditNumber</TD></TR><TR><TD>39602172154</TD><TD>396-30-4</TD><TD>396-52-0</TD></TR><TR><TD>39603152178</TD><TD>396-22-1</TD><TD>396-44-1</TD></TR><TR><TD>39603152178</TD><TD>396-22-2</TD><TD>396-44-1</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>But I WANT this...</P><TABLE border="1"><TBODY><TR><TD>CreditKey</TD><TD>OrderNumber</TD><TD>CreditNumber</TD></TR><TR><TD>39602172154</TD><TD>396-30-4</TD><TD>396-52-0</TD></TR><TR><TD>39603152178</TD><TD>396-22-1</TD><TD>396-44-1</TD></TR><TR><TD>39603152178</TD><TD>396-22-2</TD><TD>396-44-3</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>Is there a way to do this kind of one-to-one mapping?</P> Thu, 10 Jun 2021 16:53:05 GMT kate1 2021-06-10T16:53:05Z Join distinctly with non-unique key https://community.qlik.com/t5/App-Development/Join-distinctly-with-non-unique-key/m-p/1814280#M66569 <P>Let's say I have the following table parts (of two much larger tables)...</P><P>CreditTable:</P><TABLE border="1"><TBODY><TR><TD>Project</TD><TD>Contract Date</TD><TD>Value</TD><TD>Line</TD><TD>Position</TD></TR><TR><TD>396</TD><TD>2/17/21</TD><TD>54</TD><TD>52</TD><TD>0</TD></TR><TR><TD>396</TD><TD>3/15/21</TD><TD>78</TD><TD>44</TD><TD>1</TD></TR><TR><TD>396</TD><TD>3/15/21</TD><TD>78</TD><TD>44</TD><TD>3</TD></TR><TR><TD>...</TD><TD>...</TD><TD>...</TD><TD>...</TD><TD>...</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>OrderTable:</P><TABLE border="1"><TBODY><TR><TD><P>Project</P></TD><TD>Contract Date</TD><TD>Value</TD><TD>Line</TD><TD>Position</TD></TR><TR><TD>396</TD><TD>2/17/21</TD><TD>54</TD><TD>30</TD><TD>4</TD></TR><TR><TD>396</TD><TD>3/15/21</TD><TD>78</TD><TD>22</TD><TD>1</TD></TR><TR><TD>396</TD><TD>3/15/21</TD><TD>78</TD><TD>22</TD><TD>2</TD></TR><TR><TD>...</TD><TD>...</TD><TD>...</TD><TD>...</TD><TD>...</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>I need to match a credit to an order by Project, Contract Date, and Value. I need it to be a one-to-one matching. When I run my current script...</P><P>JoinedTable:</P><P>LOAD DISTINCT</P><P>Project&amp;ContractDate&amp;Value&nbsp; &nbsp;AS CreditKey</P><P>Project&amp;'-'&amp;Line&amp;'-'&amp;Position&nbsp; AS OrderNumber</P><P>RESIDENT OrderTable;</P><P>LEFT JOIN LOAD</P><P>Project&amp;ContractDate&amp;Value&nbsp; &nbsp;AS CreditKey</P><P>Project&amp;'-'&amp;Line&amp;'-'&amp;Position&nbsp; AS CreditNumber</P><P>RESIDENT CreditTable;</P><P>&nbsp;</P><P>I get something like this...</P><TABLE border="1"><TBODY><TR><TD>CreditKey</TD><TD>OrderNumber</TD><TD>CreditNumber</TD></TR><TR><TD>39602172154</TD><TD>396-30-4</TD><TD>396-52-0</TD></TR><TR><TD>39603152178</TD><TD>396-22-1</TD><TD>396-44-1</TD></TR><TR><TD>39603152178</TD><TD>396-22-2</TD><TD>396-44-1</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>But I WANT this...</P><TABLE border="1"><TBODY><TR><TD>CreditKey</TD><TD>OrderNumber</TD><TD>CreditNumber</TD></TR><TR><TD>39602172154</TD><TD>396-30-4</TD><TD>396-52-0</TD></TR><TR><TD>39603152178</TD><TD>396-22-1</TD><TD>396-44-1</TD></TR><TR><TD>39603152178</TD><TD>396-22-2</TD><TD>396-44-3</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>Is there a way to do this kind of one-to-one mapping?</P> Thu, 10 Jun 2021 16:53:05 GMT https://community.qlik.com/t5/App-Development/Join-distinctly-with-non-unique-key/m-p/1814280#M66569 kate1 2021-06-10T16:53:05Z