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: 
kaustubh
Creator
Creator

Creating key using column from two tables

Hello Everyone,

Referral:
LOAD
referralnum as "Referral Number",
acctno & '-' & patno & '-' & Ordering Provider Code as "Patient Key",
order_date,
req_visits,
external_refnum,
status as "Referral Status",
"type" as "Referral Type",
refer_from as "Ordering Provider Code",
refer_from_id,
refer_to as "Referred Provider Code", bla bla

Left Join (Referral)
LOAD
rfocode as "Ordering Provider Code",
rfoname as "Ordering Provider Name",
specialtyno as "Ordering provider Specialty",
speccode as "Ordering Provider Specialty code",
firstname as "Ordering Provider Firstname",
lastname as "Ordering Provider Lastname", bla bla

 

Above is my code. i am trying to produce a "charge key" in table 1 using the column highlighted after left join in table 2.

I know the concept of load but being new to Qlik I donno where to put it and how exactly to implement it. Can someone please help me with syntax ad two lines of explanations for thorough knowledge?

4 Replies
rubenmarin

You will need one (or many) field that relates both tables, in example, if table 2 has also the 'referralnum' field you can join or use a map with that field, ie:
mapProviderCode:
Mapping LOAD referralnum, "Ordering Provider Code" FROM/Resident ....;

Referral:
LOAD
referralnum as "Referral Number",
acctno & '-' & patno as "Patient Key",
acctno & '-' & patno & '-' & ApplyMap('mapProviderCode', referralnum) as "Charge Key",
kaustubh
Creator
Creator
Author

I dont wish to have a left join here. My question is how can I produce a key with "Ordering Provider Code" being in second table which gets added after left join.

dwforest
Specialist II
Specialist II

have to do the join in a temp table, then add key with a load * RESIDENT temp_table and drop the temp table afterwards
kaustubh
Creator
Creator
Author

Can you please help me with script? I am new to Qlik. I am having a hard time to understand where should I insert the temp table?