Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
can you describe exactly how your input tables translate to your expected output?
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.
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];