17 Replies Latest reply: May 11, 2010 1:51 PM by John Witherspoon RSS

    reg---Self join in a table

    Bharathe11dec

      Hi all,

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

      empid is same as managerid.

       

      empidnamemgid
      1Alex3
      2John1
      3Ram1
      4Sham2


      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

        • Re. :reg---Self join in a table
          Martin FAVIER

          Hello Bharathe,

          Look this application.

          Martin

            • Re. :reg---Self join in a table
              Bharathe11dec

              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

                • Re. :Re: Re. :reg---Self join in a table
                  Martin FAVIER

                  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

                    • Re. :Re: Re. :reg---Self join in a table
                      Bharathe11dec

                      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

                        • Re. :Re: Re. :reg---Self join in a table
                          Bharathe11dec

                          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

                            • Re. :Re: Re. :reg---Self join in a table
                              John Witherspoon

                              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
                              ;

                                • Re. :Re: Re. :reg---Self join in a table
                                  Bharathe11dec
                                  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
                                    • Re. :Re: Re. :reg---Self join in a table
                                      Bharathe11dec

                                      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

                                        • Re. :Re: Re. :reg---Self join in a table
                                          Bharathe11dec

                                          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

                                            • Re. :Re: Re. :reg---Self join in a table
                                              juergm

                                              Hi

                                              try in your load script

                                              YourTable:
                                              Load * from selfjoin.xls (...)

                                              this will give you a table in QlikView named "YourTable"

                                              now add the join according to Johns example.

                                              Regards

                                              Jürg

                                                • Re. :Re: Re. :reg---Self join in a table
                                                  Bharathe11dec

                                                  Hi Juerg Maier JmiD GmbH Schweiz,

                                                  i write the code like this....

                                                   

                                                   

                                                   

                                                   

                                                   

                                                   



                                                   

                                                   

                                                   

                                                   

                                                   

                                                   



                                                   

                                                   

                                                  LEFT

                                                   

                                                   

                                                  LOAD

                                                   

                                                   

                                                  name as "Manager Name"

                                                  RESIDENT

                                                   

                                                   

                                                  biff, embedded labels, table is Sheet1$);

                                                  self.xls

                                                  (

                                                  empid as mgid, JOIN (self.xls)

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



                                                  Its showing error while reloading as

                                                  Table not found
                                                  LEFT JOIN (self.xls)
                                                  (biff, embedded labels, table is Sheet1$)

                                                   

                                                  Table not found
                                                  LOAD
                                                  empid as mgid
                                                  ,name as "Manager Name"
                                                  RESIDENT self.xls
                                                  (biff, embedded labels, table is Sheet1$)

                                                   

                                                  what to do now????

                                                  bharathe

                                                   

                                                   



                                                    • Re. :Re: Re. :reg---Self join in a table
                                                      Bharathe11dec

                                                      hi all ....

                                                      please guide me in solving this problem...

                                                      thanks in advance

                                                      bharathe

                                                        • Re. :Re: Re. :reg---Self join in a table
                                                          John Witherspoon

                                                           


                                                          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.

                                              • Re. :Re: Re. :reg---Self join in a table
                                                Anita Melbye

                                                Hi John, or someone else :-)

                                                Maybe this is not the right place to ask this question, but it has something to do with left joins, so I give it a shoot :-)

                                                I have 3 tables in my report, and I only want the orders witch are listed in my first table. Therefor, I have a Left Join on my 2'nd table load, in order to keep out any orders that are not listed in table 1.

                                                But what can I write in my 3 load, in order to only keep the orders from Table 1?

                                                If I write Left join on Table 3, my raport gets messed up. And if I leave out the Left Join, I get all the extra orders from table 3 in addition..

                                                I hope I have been able to make my challenge clear for you to understand.

                                                 

                                                Table 1Table 2Table 3Orders wanted in report
                                                101010
                                                111111
                                                121212
                                                13131313
                                                1414
                                                1515
                                                16
                                                17
                                                18
                                                19
                                                2020
                                                212121
                                                222222


                                                I appreciated any help I can get, and I am so thankful for anybody wanting to try to help me :-)

                                                *Newbee in love*

                                                 

                                                  • Re. :Re: Re. :reg---Self join in a table
                                                    John Witherspoon

                                                     


                                                    Anita Melbye wrote: I have 3 tables in my report, and I only want the orders witch are listed in my first table. Therefor, I have a Left Join on my 2'nd table load, in order to keep out any orders that are not listed in table 1.
                                                    But what can I write in my 3 load, in order to only keep the orders from Table 1?
                                                    If I write Left join on Table 3, my raport gets messed up. And if I leave out the Left Join, I get all the extra orders from table 3 in addition..




                                                    You may have to post some sample data, because based on my understanding of what you're saying you're doing, there should be no problem, and you should get the exact result you posted. It sounds like you're doing something like this:

                                                    Table:
                                                    LOAD
                                                    Order
                                                    ,Something
                                                    FROM Table1
                                                    ;
                                                    LEFT JOIN (Table)
                                                    LOAD
                                                    Order
                                                    ,SomethingElse
                                                    FROM Table2
                                                    ;
                                                    LEFT JOIN (Table)
                                                    LOAD
                                                    Order
                                                    ,SomeOtherField
                                                    FROM Table3
                                                    ;

                                                    When you do the third load, the only Orders in Table should be the Orders originally loaded from Table1. Therefore, the left join should only take those orders from Table3, just like it did with Table2.