6 Replies Latest reply: Feb 14, 2011 11:42 AM by Björn Wedbratt RSS

    Using Nested SELECT when importing data

      I have two SQL server tables, an Engineer table and an Incident table. I'd like to link these tables using the engineer name field.

      Is it possible in the edit script dialog to do the following

       

       

       

       



       

       

      SQL
      SELECT ID as EngineerID, ManagerID, TeamID, EngineerName FROM Engineer;

      SQL
      SELECT ID as IncidentID, EngineerID,
      (SELECT EngineerName FROM Engineer WHERE Engineer.ID = Incident.EngineerID) AS EngineerName
      FROM Incident;

       



       

        • Using Nested SELECT when importing data
          Miguel Angel Baeyens de Arce

          Hi,

          I'd do something like the following:

           

          EngineerIDNameMap:MAPPING LOAD ID, Name;SQL SELECT ID, Name FROM Engineer; Data:LOAD ID AS IncidentID, EngineerID, ApplyMap('EngineerIDNameMap', EngineerID) AS EngineerName; // this will return the corresponding value for the ID previously loaded in the table aboveSQL SELECT ID, EngineerID FROM Incident;


          Further information can be found in the Reference Manual about mapping tables.

          Hope that helps.

          • Using Nested SELECT when importing data
            Richard Cripps

            Does it error when you do it?

            Although I am not sure why you want to do it as if you loaded in :

            SQL
            SELECT ID as EngineerID, ManagerID, TeamID, EngineerName FROM Engineer;

            SQL
            SELECT ID as IncidentID, EngineerID
            FROM Incident;

            QV Would create the associative join on Engineer ID so you would be able to view IncidentID next to EngineerName in the front end.

              • Using Nested SELECT when importing data

                I had expected that QV would create the link on the EngineerID in each table. I have a sheet with a list box showing the engineer names from the engineer table.

                I also have a bar chart showing the incidents from each engineer.

                The bar chart includes a total column and this correctly shows the total number of rows as 716. But the bars for each engineer don't show the correct totals.

                I can't figure out why and wanted to see if linking by the engineer name would make any difference.

                  • Using Nested SELECT when importing data
                    Björn Wedbratt

                    If EngineerID is named exactly the same in the two tables, QlikView will associate the fields into one. You can check this using the table-viewer in QV (CTRL+T), making sure there's a link between the tables on the field. However this is just the first step, QlikView will then make associations on the exact fieldvalues. So if you experience that you have values in the EngineerID field from both tables, you might need to also reformat the actual data.

                    Let's say that you have EngineerID='A' in one table and EngineerID='<space>A' in the other. In this case you will see the value 'A' and '<space>A' in the listbox, hence not associated. Then you need to format the values to be exact using various script-statements.

                    If you experience that you have EngineerID='123' left-aligned in the listbox, but also '123' right-aligned it indicates that QlikView formated the left-aligned '123' as a string, and not a number. Again, the values will not be the same, hence no association, and the data needs to be number/text-formated

                    You could of course join the two tables, either in the SELECT-statement, or using JOIN between two LOAD-statements in QlikView, but normally this is not required, unless you need to re-structure your schema into a star-schema.

                    Regards,
                    Bjorn

                      • Using Nested SELECT when importing data

                        Thanks for all the help...by a process of elimination I manged to figure out what was happening..and I guess if I'd have expanded on all the tables I was using you'd all have probably figure it out way before me.

                        As it happens I had a couple of other tables imported as well and I hadn't appreciated that QV had built links between these tables using another field and this was effectively executing a query on the linked tables that was reducing the dataset returned.

                        Once I stopped loading the field that was common across all the tables I was getting the correct data returned.

                        I obviously have a lot to learn about QV and thanks to all your help this morning I have learned a bit more about how QV is working.

                        Thanks again.