Solved: reg---Self join in a table - Qlik Community

Report Inappropriate Content · ‎2010-05-10

Hi all,

In a excel sheet i m having empid,empname,managerid.

empid is same as managerid.

empid	name	mgid
1	Alex	3
2	John	1
3	Ram	1
4	Sham	2

here i want to display the names of manager.

If i click 1 in empid, i want to generate Ram as manager name....

If i click 2&3 in empid ,i want to display Alex as manager name....

if 4 in empid, i want to display Johnas manager name...

In the source file no manager name is given.

How to achieve this?

thanks in advance,

Bharathe

johnw · ‎2010-05-11


Bharathe11dec wrote:please guide me in solving this problem...

I gave you half the script, Jürg gave you the other half, and he told you to put his half first, my half second. Doing exactly what he said, you'd get this:

YourTable:
Load * from selfjoin.xls (biff,embedded labels,table is sheet1$)
;
LEFT JOIN (YourTable)
LOAD
empid as mgid
,name as "Manager Name"
RESIDENT YourTable
;

And that should work. It may not be exactly what you want, though. Presumably, you've already written the script to load from selfjoin.xls. We're not telling you to change that script AT ALL. All we're telling you to do is add MY script after it, and replace "YourTable" with the name you gave your table (and not with selfjoin.xls, which is your data source, not the name of your table). If your table is named "Employees" for instance, replace "YourTable" with "Employees" in the two spots it appears in the sample script I gave you.

View solution in original post

martin59 · ‎2010-05-10

Hello Bharathe,

Look this application.

Martin

Report Inappropriate Content · ‎2010-05-10

hi Martin....

i got only personal version of Qlik software. i cant open the atachment file that u have send. could u please explain in words.

thanks

Bharathe

martin59 · ‎2010-05-10

You can reload this script and see what's happened. I think it's the best way to understand.

Employee:
HIERARCHY (EmployeeID, MgrID, Employee, Manager, Employee, Path, '/', Depth)
LOAD EmployeeId as EmployeeID,
 Name as Employee,
 MgrID,
 Salary;
LOAD * INLINE [
 EmployeeId, Name, MgrID, Salary
 1, Anthony, 5, 8000
 2, Peter, 5, 8500
 3, Keanu, 5, 10000
 4, William, 5, 20000
 5, Jessy, 5, 50000
 6, Toto, 3, 40000
 7, Titi, 3, 20000
];

Martin

Report Inappropriate Content · ‎2010-05-10

martin...

when i click employeeid it is showing only empid,empname some times .... its not showing the associated data (ie white background) in manager name and manager id....

some times this problem is occuring when we click 3 in empid sometimes in empid 5.....

why such problem is occuring?

bharathe

Report Inappropriate Content · ‎2010-05-10

hi martin,

In this... 3 and 5 are the manager id.... problem is occuring only we select 3 0r 5 in empid its not showing related data in manager id and manager name.

bharathe

johnw · ‎2010-05-10

If you're only going up one level in the hierarchy, then perhaps this is a simpler solution than a hierarchy load:

LEFT JOIN (YourTable)
LOAD
empid as mgid
,name as "Manager Name"
RESIDENT YourTable
;

Report Inappropriate Content · ‎2010-05-11

hi john, In the above code, in which formate should i give the table name. my source file is a excel sheet. while loading the excel file, it will be selfjoin.xls (biff,embedded labels,table is sheet1$); if i replace the table name line with above lines its showing error while reloading. in which formate should I write the table name in (YourTable) in above coding thanks bharathe

Report Inappropriate Content · ‎2010-05-11

Hi john,

In the above code, in which formate should i give the table name. my source file is a excel sheet.

while loading the excel file, it will be

selfjoin.xls

(biff,embedded labels,table is sheet1$);

if i replace the table name line with above lines its showing error while reloading.

in which formate should I write the table name in (YourTable) in above coding

thanks

bharathe

Report Inappropriate Content · ‎2010-05-11

hi all.....

In the above code(code given by john), what should i want to give in the (Table Name) ?

my source file name is selfjoin.xls

if i replace (Table Name) with selfjoin.xls an error is popped out while reloading.

what should i want to write in the table name ?

or any other solution is there for this????

plz help me.

bharathe