Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
NewToQlik
Creator
Creator

Joining tables

Hi all, I have the following tables.


ADM_APPL:

EMPLIDACAD_CAREERSTDNT_CAR_NBRLAST_SCH_ATTEND
1000001A0E00001
1000002B0E00002
1000003C0E00001


EXT_ORG :

EXT_ORG_IDLS_SCHOOL_TYPE
E00001
University
E00002
College
E00003
High School

For all EMPLID, I want to get the LS_SCHOOL_TYPE where the values in LAST_SCH_ATTEND=EXT_ORG_ID.
EG. EMPLID 1000001 should show University.

       EMPLID 1000002 should show College.

       EMPLID 1000003 should show University

How do I do this?

Much help is appreciated. Thank you so much.

8 Replies
dplr-rn
Partner - Master III
Partner - Master III

for qlik to link 2 tables you need same column names. rename last school attended to EXT_ORG_ID

rubenmarin

Hi Wong, tables in Qlik are joined using fields names: two tables with the same field are joined using that field values.

In your case you can rename EXT_ORG_ID to LAST_SCH_ATTEND, and use Join to keep the values in one table:

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/ScriptPrefixes...


ADM_APPL:

LOAD [FieldNames] From...;


Left Join

LOAD EXT_ORG_ID as LAST_SCH_ATTEND, LS_SCHOOL_TYPE...

NewToQlik
Creator
Creator
Author

This are my tables

ADM_APPL:

IDCAREERCAR_NBRLAST_SCH_ATTEND
1000006MIT0E0000000001
1000008MAR0E0000000002
1000008UGRD0E0000000002
1000010PHD0E0000000003
1000010UGRD0E0000000003

EXT_ORG:

    

EXT_ORG_IDEFFDTEFF_STATUSLS_SCHOOL_TYPE
E00000000011905-01-01 00:00:00.000AUNV
E00000000021905-01-01 00:00:00.000AUNV
E00000000031905-01-01 00:00:00.000AUNV
E00000000041905-01-01 00:00:00.000AHIGH SCHOOL
E00000000051905-01-01 00:00:00.000ACOLLEGE

I tried renaming the EXT_ORG_ID to LAST_SCH_ATTEND and left joined, but the result gave many ID and LS_SCHOOL_TYPE duplicates. Any ideas?

Anonymous
Not applicable

Hi,

Use Join and check

As per your output LS_School_type value is Different for corresponding EXT_ORG_ID

arrange the data as per your scenario and try it may work.

Thanks

NewToQlik
Creator
Creator
Author

What do you mean join and check?

Anonymous
Not applicable

Hi

Validate your data as per you scenario and use Join

Thanks

rubenmarin

Hi, in this tables I already see duplicates por 1000008 and 1000010, caused by different careers, if the table EXT_ORG has the field EXT_ORG_ID as uniquie identifier, and this field is the only one with the same name between both tables, there should be no duplicates created by join.


You can also use a mapping table to avoid creating duplicates with join but I would try to look the real reason of the duplicates to undertand whats happening.

https://help.qlik.com/es-ES/qlikview/November2017/Subsystems/Client/Content/Scripting/ScriptPrefixes...

Mark_Little
Luminary
Luminary

Hi,

When you are wanting to join one field from one table to another in Qlik you should ideally use an apply map.

https://help.qlik.com/en-US/sense/September2018/Subsystems/Hub/Content/Sense_Hub/Scripting/MappingFu...

If there is more than one field then you would look at joining.

Mark