topic Using Nested SELECT when importing data in QlikView

Using Nested SELECT when importing data

Mon, 14 Feb 2011 13:09:49 GMT

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 — Mon, 14 Feb 2011 13:28:57 GMT

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

richardcripps — Mon, 14 Feb 2011 13:34:07 GMT

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

Mon, 14 Feb 2011 13:42:41 GMT

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

Bjorn_Wedbratt — Mon, 14 Feb 2011 14:59:44 GMT

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

Mon, 14 Feb 2011 16:26:28 GMT

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.

Using Nested SELECT when importing data

Bjorn_Wedbratt — Mon, 14 Feb 2011 16:42:52 GMT

Great that you solved your issue! Happy Qliking 🙂