Creating key using column from two tables

[kaustubh]

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?

[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]

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]

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]

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?