topic Re: Creating key using column from two tables in App Development

Creating key using column from two tables

kaustubh — Thu, 28 Feb 2019 20:40:38 GMT

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?

Re: Creating key using column from two tables

rubenmarin — Thu, 28 Feb 2019 19:01:43 GMT

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",

Re: Creating key using column from two tables

kaustubh — Thu, 28 Feb 2019 20:44:20 GMT

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.

Re: Creating key using column from two tables

dwforest — Thu, 28 Feb 2019 21:45:11 GMT

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

Re: Creating key using column from two tables

kaustubh — Mon, 04 Mar 2019 15:59:26 GMT

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?