topic Re: Created a left join to get Svc Mgr Names but it's not linked to other tables. in QlikView

Created a left join to get Svc Mgr Names but it's not linked to other tables.

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

Hi! I'm trying to get the names of the Svc Mgrs from Employee table by getting the Svc Mgr ID from the Customer table and left joined it to the Employee table and I was able to get the Svc Mgrs names. So that when I click on a Svc Mgr ID, it's giving me the corresponding Svc Mgr's name. However, the other fields from other tables (calendar and transaction tables) are greyed out. What should I do so that when I click on the Svc Mgr's name, it's also linked to the other tables? Thank you for your help in advance

EMPLOYEE:
LOAD
       IF (NOT ISNULL(EmployeeID), EmployeeID,0) AS EmployeeID
      ,[LoginID]
      ,[FirstName]
      ,[LastName]
FROM $(vQVDPath)\Employee.qvd
(qvd);

//get the Svc Mgr ID from Customer table and join with EmployeeID from Employee table to get the Svc Mgr Names
left join (EMPLOYEE)
LOAD distinct
[Svc Mgr ID] as EmployeeID,
1 as SMID_Filter
FROM $(vQVDPath)\Customer.qvd
(qvd);

CUSTOMER:
LOAD
     Customer_ID,
     Name,
     City,
     [Zip Code]

FROM
$(vQVDPath)\Customer.qvd
(qvd);

Re: Created a left join to get Svc Mgr Names but it's not linked to other tables.

Anil_Babu_Samineni — Fri, 26 May 2017 04:46:48 GMT

I haven't seen any field related [Svc Mgr ID] in EMPLOYEE table

Re: Created a left join to get Svc Mgr Names but it's not linked to other tables.

xarapre7 — Fri, 26 May 2017 04:58:52 GMT

Sorry.... Customer Table has the field [Svc Mgr ID] which joins to the EmployeeID of the Employee Table. That's how they are associated.

Re: Created a left join to get Svc Mgr Names but it's not linked to other tables.

Anil_Babu_Samineni — Fri, 26 May 2017 05:03:08 GMT

Ok, Try this?

EMPLOYEE:
LOAD
      IF (NOT ISNULL(EmployeeID), EmployeeID,0) AS EmployeeID
      ,[LoginID]
      ,[FirstName]
      ,[LastName]
FROM $(vQVDPath)\Employee.qvd (qvd);

CUSTOMER:
LOAD
    Customer_ID,
    Name,
    City,
    [Zip Code]

FROM
$(vQVDPath)\Customer.qvd (qvd);

left join (EMPLOYEE)
LOAD distinct
[Svc Mgr ID],

ApplyMap('EMPLOYEE',EmployeeID) as EmployeeID,

//ApplyMap('EMPLOYEE',[Svc Mgr ID],EmployeeID) as EmployeeID,
1 as SMID_Filter
Resident CUSTOMER;

Re: Created a left join to get Svc Mgr Names but it's not linked to other tables.

prma7799 — Fri, 26 May 2017 05:03:26 GMT

Then write this

left join (EMPLOYEE)

CUSTOMER:
LOAD

     [Svc Mgr ID] as EmployeeID
     Customer_ID,
     Name,
     City,
     [Zip Code]

FROM
$(vQVDPath)\Customer.qvd
(qvd);

Re: Created a left join to get Svc Mgr Names but it's not linked to other tables.

xarapre7 — Fri, 26 May 2017 05:37:21 GMT

I'm getting an error.

where is the mapping?

Re: Created a left join to get Svc Mgr Names but it's not linked to other tables.

xarapre7 — Fri, 26 May 2017 05:42:49 GMT

There's an error.

Syntax error, missing/misplaced FROM:

left join (EMPLOYEE)

CUSTOMER:

Re: Created a left join to get Svc Mgr Names but it's not linked to other tables.

prma7799 — Fri, 26 May 2017 05:49:50 GMT

Try this Or please share some demo qvw file

left join (EMPLOYEE)

LOAD
    Customer_ID,
    Name,
    City,
    [Zip Code]

FROM
$(vQVDPath)\Customer.qvd (qvd);

Re: Created a left join to get Svc Mgr Names but it's not linked to other tables.

xarapre7 — Fri, 26 May 2017 06:30:05 GMT

Here is the file for your reference. Please help.

Thanks.

Re: Created a left join to get Svc Mgr Names but it's not linked to other tables.

prma7799 — Fri, 26 May 2017 06:33:55 GMT

Please check..

Re: Created a left join to get Svc Mgr Names but it's not linked to other tables.

xarapre7 — Fri, 26 May 2017 06:44:54 GMT

Did you add anything to the file? I don't see anything added or modified.

Re: Created a left join to get Svc Mgr Names but it's not linked to other tables.

prma7799 — Fri, 26 May 2017 06:56:44 GMT

Have you check

I think it is reload successfully ..

I have remove , (comma) from Country

CUSTOMER:

LOAD

Customer_ID,

Name & ' (' & replace(ltrim(replace(ltrim(Customer_ID),'0',' ')),' ','0') & ')' AS 'Customer',

Name,

City,

[Zip Code],

IF(IsNull(State)=0, State,'<NA>') AS CustomerState,

IF (NOT isnull(Country) AND trim(Country)<>'',Country, '<NA>') AS 'Country'

FROM

$(vQVDPath2)\qvCustomer.qvd

(qvd);

Re: Created a left join to get Svc Mgr Names but it's not linked to other tables.

xarapre7 — Fri, 26 May 2017 07:09:32 GMT

It's successfully reloading but, the SVC Managers are still not connected to the other tables. If I choose any of the SVC Managers, it's not linked to any of the dates or transaction. That is the issue that I'm having.

Re: Created a left join to get Svc Mgr Names but it's not linked to other tables.

prma7799 — Fri, 26 May 2017 07:11:46 GMT

Can you please provide qvd's for all tables

Re: Created a left join to get Svc Mgr Names but it's not linked to other tables.

Kushal_Chawda — Fri, 26 May 2017 07:23:28 GMT

see the attached