topic Join 2 tables with like condition in App Development

Join 2 tables with like condition

kenny7 — Tue, 25 Oct 2022 09:50:55 GMT

Hi,

As I know QlikSense automatically join tables based on common column name.

But how can I customize it by joining 2 tables with the same column name with LIKE condition?

table A.serial number LIKE '%table B.serial number%'

Table A

Serial Number	Date
a,b,c	10/21/2022
,b,c	10/22/2022
a,,	10/23/2022

Table B

Serial Number	Date
a	10/21/2022
c	10/21/2022

Final expected output

Table A Serial Number	Date	Table B Serial Number	Date
a,b,c	10/21/2022	a	10/21/2022
a,b,c	10/21/2022	c	10/23/2022
,,c	10/21/2022	c	10/23/2022
a,,c	10/21/2022	a	10/21/2022
a,,c	10/21/2022	c	10/23/2022

Thanks

Re: Join 2 tables with like condition

Mark_Little — Tue, 25 Oct 2022 14:36:55 GMT

HI @kenny7

I don't believe their is a similar join function in Qlik, you would have to create the a new Field in table one the breaks the individual Serial Numbers out and then join on this field and drop it if no longer needed.

Re: Join 2 tables with like condition

MarcoWedel — Tue, 25 Oct 2022 16:19:06 GMT

can you describe exactly how your input tables translate to your expected output?

Re: Join 2 tables with like condition

kenny7 — Wed, 26 Oct 2022 01:42:28 GMT

Thanks for your input Mark.

I have the same thought too but because of we are going to use it as 1 of the filters, so we decided to combine all serial number in 1 column rather than break into 3 different columns with 3 filters for users. Then user will have to figure out which column contain the serial number that they are looking for.

Re: Join 2 tables with like condition

kenny7 — Wed, 26 Oct 2022 02:08:38 GMT

Hi @MarcoWedel ,

Kindly refer to the following script on how we join 2 tables together, i still haven't figured out how to perform left join with LIKE condition

[TableA]:

LOAD

serial_number As "Serial Number",
Date;
SELECT "serial_number",
Date1
FROM "TableA"

[TableB]:
Left Keep(TableA)
SQL SELECT
"SN",
"Date"
FROM "TableB";

LEFT JOIN ([TableA])
Load
"SN" AS "Serial Number",
"Date2"
RESIDENT [TableB];
DROP TABLE [TableB];