Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all, I have the following tables.
ADM_APPL:
EMPLID | ACAD_CAREER | STDNT_CAR_NBR | LAST_SCH_ATTEND |
---|---|---|---|
1000001 | A | 0 | E00001 |
1000002 | B | 0 | E00002 |
1000003 | C | 0 | E00001 |
EXT_ORG :
EXT_ORG_ID | LS_SCHOOL_TYPE | |
---|---|---|
| University | |
| College | |
| 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.
for qlik to link 2 tables you need same column names. rename last school attended to EXT_ORG_ID
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:
ADM_APPL:
LOAD [FieldNames] From...;
Left Join
LOAD EXT_ORG_ID as LAST_SCH_ATTEND, LS_SCHOOL_TYPE...
This are my tables
ADM_APPL:
ID | CAREER | CAR_NBR | LAST_SCH_ATTEND |
1000006 | MIT | 0 | E0000000001 |
1000008 | MAR | 0 | E0000000002 |
1000008 | UGRD | 0 | E0000000002 |
1000010 | PHD | 0 | E0000000003 |
1000010 | UGRD | 0 | E0000000003 |
EXT_ORG:
EXT_ORG_ID | EFFDT | EFF_STATUS | LS_SCHOOL_TYPE |
E0000000001 | 1905-01-01 00:00:00.000 | A | UNV |
E0000000002 | 1905-01-01 00:00:00.000 | A | UNV |
E0000000003 | 1905-01-01 00:00:00.000 | A | UNV |
E0000000004 | 1905-01-01 00:00:00.000 | A | HIGH SCHOOL |
E0000000005 | 1905-01-01 00:00:00.000 | A | COLLEGE |
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?
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
What do you mean join and check?
Hi
Validate your data as per you scenario and use Join
Thanks
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.
Hi,
When you are wanting to join one field from one table to another in Qlik you should ideally use an apply map.
If there is more than one field then you would look at joining.
Mark