Qlik Community

Ask a Question

Community Corner

Discussion board for questions about the Qlik Community, its features, sharing information, general discussions and even some fun. This is for both new and longtime community members. Everyone is welcome!

Announcements
3 Keynotes, 100+ Breakout Sessions, 1 New York Times bestseller, and you. QlikWorld Online, May 10-12: REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
selma147
Contributor II
Contributor II

check if a column contain the value from another Table's column

Hi,

I have a data model of 2 tables A and B:

[A]:

SELECT

Name,

Departement,

Profession

FROM "public"."A";

 

[B]:

SELECT

              Title

FROM "public"."B";

I would like to add a new column in Table A and test if the column Profession contain the content of the column Title from table B

The new column should be exactly the column Title but in table A.

For example:

If I have A.Profession: Responsible of HR and in B.Title I have HR value

The value of the new column should be HR.

Could you please help me with this?

Thank you in advance,

Regards,

Selma

3 Replies
lblumenfeld
Luminary Alumni
Luminary Alumni

Does table B contain only on row, or multiple rows? If the latter then do you mean

For each record in table A
   Search all the records in table B and if the value of A.Profession contains the value of B.Title from any record in B then
       Make A.NewColumn = B.Title

Let me know if this is what you're asking. Thanks.

selma147
Contributor II
Contributor II
Author

Yes this is the requirements.
Thanks a lot,
Selma
marcus_sommer
MVP & Luminary
MVP & Luminary

Usually it's a job for a mapping, for example with something like:

[B]: mapping load Title as Lookup, Profession as Return;
        SELECT Title, Profession FROM "public"."B";

[A]: SELECT Name, Departement, Profession, applymap('B', Profession, 'Default') as New FROM "public"."A";

But in your case you will need a second column containing the return-values because only with one column Title = Profession or it does not match. I assume here that your description is just not complete.

Thinkable are also approaches which search for sub-strings but in this case you will need a defined logic which sub-strings will lead to a valid matching.

- Marcus