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: 
sunil-kumar5
Creator II
Creator II

Value from another table

Hello All,

I have 3 tables Table1,Table2 and Table3. I have already used left keep on table2 with reference to table1 on my other sheet as dashboard. Now I am making the another dashboard which have the requirement from all three tables. All the three table has columns Name and ID, Also table 3 has unique name and ID only.  I am using one filter for Name from table3 as Name but on my KPI I want when I select any name from table 3, my KPI should reflect count of ID's from table1.

I hope, I am able to make it clear. Please advise.

Thanks

9 Replies
Prashant_N
Contributor III
Contributor III

Hi,

As you know about the data of table 3 that its unique. you can directly left join the table 3 with table 1 on the basis of Name & ID by creating a composite key of both.

Like this.

Table 1:

load Name&'-'&ID as Joinkey,Name,ID,Measure from table 1;

left join(Table 1)

Load Name&'-'&ID as Joinkey,Name as table3_Name from table 3;

Hope it helps

Regards,

Prashant

sunil-kumar5
Creator II
Creator II
Author

Hi Prashant,

Its not giving the desired result, same issue.

Thanks

Prashant_N
Contributor III
Contributor III

Please check the data of fields that are used in creating Joinkey. might be the case where key values are not same in both the table.

Regards,

Prashant

sunil-kumar5
Creator II
Creator II
Author

Table3 has the record of name and id , Table1 has the same name and ID those who received leads from us.

Prashant_N
Contributor III
Contributor III

can you provide a dummy data so i can help better

sunil-kumar5
Creator II
Creator II
Author

Please find the attached sample data for all three tables. I need a count of Lead from table 1 but partner name will be filtered from table 3. Total Count is 18 but if choose any partner name from table3 it should reflect the count as per that.

sunil-kumar5
Creator II
Creator II
Author

I just checked, based on this sample data I am getting the desired result without any left join but not sure as why it is not coming in my dashboard even I have duplicate the table1 to avoid anything and join separately.

Prashant_N
Contributor III
Contributor III

Hi,

This script might help

Table1:
LOAD
PartnerName&'-'&PartnerID&'-'&LeadType as Tb2Joinkey,
PartnerName&'-'&PartnerID as Tb3Joinkey,
PartnerName,
PartnerID,
Lead,
LeadType
// Country
// Zone
FROM [lib://Con/SampleData.xlsx]
(ooxml, embedded labels, header is 1 lines, table is Sheet1);

Table2:
Left join(Table1)
Load
PartnerName1&'-'&PartnerID1&'-'&LeadType1 as Tb2Joinkey,
// PartnerName1,
// PartnerID1,
// LeadType1,
LeadStatus
FROM [lib://Con/SampleData.xlsx]
(ooxml, embedded labels, header is 1 lines, table is Sheet1);

table3:
Left join(Table1)
Load
PartnerName2&'-'&PartnerID2 as Tb3Joinkey,
// PartnerName2,
// PartnerID2,
Status,
Country1 as Country,
Zone1 as Zone,
Mobile
FROM [lib://Con/SampleData.xlsx]
(ooxml, embedded labels, header is 1 lines, table is Sheet1);

Regards,

Prashant

Ksrinivasan
Specialist
Specialist

Hi,

table1:
LOAD
PartnerName,
PartnerID,
Lead,
LeadType,
Country,
Zone
FROM ampleData112.xlsx]
(ooxml, embedded labels, table is Sheet1);
Left join
LOAD
PartnerName,
PartnerID,
LeadStatus,
LeadType
FROM SampleData112.xlsx]
(ooxml, embedded labels, table is [Sheet1 (2)]);

table3:
LOAD
PartnerName,
PartnerID as PartnerID_T3,
Status,
Country as Country_T3,
Zone as Zone_T3,
Mobile
FROM SampleData112.xlsx]
(ooxml, embedded labels, table is [Sheet1 (3)]);

 

In chart Expression:

1. Filter pane DIM as =PartnerName

2. Select KPI and Expression is = Count(Country)

result is blow:

1. without selection

Ksrinivasan_0-1610644368324.png

 

2. if you select Mahender

Ksrinivasan_1-1610644444569.png

regards,

Ksrinivasan