topic Re: Hi,how to create the link between the two fields in QlikView

Hi,how to create the link between the two fields

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi i am having the column Report To in Employee table and Report Id and Report Name in Reporting Table :

For Example

Employee Table :

Employee,Report To

abc , 20

asd , 30

Reporting Table

Report ID, Report Name.

20 , manager

30 , Account

i have created Row No for both the table as a key field

if i select Account it is not displaying the list of employee reporting to Him.

Kindly help me out

Expected Output

Employee,Report to,Report Name

ABC ,20 ,Manager

ACD ,30 ,Account

ABD ,30 ,Account

Re: Hi,how to create the link between the two fields

sudeepkm — Tue, 19 Sep 2017 18:11:54 GMT

you may try this.

Employee:

Load Employee, [Report To] as [Report ID] from <Employee Table>;

Report:

Load [Report ID], [Report Name] from <Report Table>;

Or you can use apply map to load respective Report Names from Report table to Employee table.

Re: Hi,how to create the link between the two fields

Anonymous — Tue, 19 Sep 2017 18:52:12 GMT

After applying your condition the Reporter name displaying first and Employee name is blank

please see the attachment

Re: Hi,how to create the link between the two fields

Anonymous — Tue, 19 Sep 2017 18:55:48 GMT

for mapping load we should only have two fields ,

but in reporting table i am having row no,reporter id,reporter name

do the needful

Re: Hi,how to create the link between the two fields

trdandamudi — Tue, 19 Sep 2017 19:04:57 GMT

Is this what you are looking for ?

EmployeeTable:
Load
Employee, [Report To] as [Report ID];
Load * Inline [
Employee,Report To
abc,20
asd,30
];

Inner join(EmployeeTable)
ReportingTable:
Load * Inline [
Report ID, Report Name
20, manager
30, Account
];

Re: Hi,how to create the link between the two fields

sudeepkm — Tue, 19 Sep 2017 19:08:48 GMT

you can remove the rowno() from the Reporting table if you want to use the mapping load.

in that way you can load Report Name based on matching Report ID to your Employee table.

Is it possible for you to share sample/scrambled application.

Re: Hi,how to create the link between the two fields

Anonymous — Tue, 19 Sep 2017 19:27:53 GMT

sudeepkm

Hi ,it is working Fine with the Apply Map after removing Rowno() function in Reporting Table.

trdandamudi

IT is working Fine With Inner Join Also

kindly advice me,

Performance wise which one is better appymap() or Innerjoin()

Re: Hi,how to create the link between the two fields

mjayachandran — Tue, 19 Sep 2017 19:51:25 GMT

There is no absolute truth that tells you when to use applymap or joins. You should always try what you think is best. It depends on how much data you want to combine. A Join is very flexible but takes away memory resources like anything. Mapping tables are extremely fast but for smaller data sets it doesn't matter.

Tyr to Avoid JOINs especially on large data sets. Use Applymap() for conversions and translations of single fields, or multiple Mapping tables for multiple fields in large data sets

But many a times developers tend to prefer applymap to any JOIN simply because of its efficiency

Re: Hi,how to create the link between the two fields

Anonymous — Tue, 19 Sep 2017 20:13:55 GMT

Thanks for Explanation Mahesh.