First steps in QV - sample database "Northwind"

[datanibbler]

Hi,

I am just working my way through the tutorial "First steps" and I suppose my company's firewall has blocked the "salespersons" database that's included with the exercising files. So I had a look for another database I could use to try this process and I found the MS_Access sample database "Northwind", which is actually better in a way since it has not only one, but many tables to select from.

That is also my question now: How can I, in the "select tables and fields", dialog select (fields from) two or more tables? The usual way to do that would be to hold the Ctrl key depressed while selecting, but that doesn't seem to work here.

Can anybody help me there?

Many thanks!

Best regards,

DataNibbler

[hic]

This is the core QlikView logic: You shouldn't select two fields from different tables. Instead, you load them as two tables and make sure that the key between the two tables is named the same in both.

Attached you will find a script that loads Northwind on my computer. You need to change the Connect string.

HIC

[datanibbler]

Hi,

thank you! I nearly guessed so much; So I have to do it in several steps, connecting to the DB only once, but adding several SELECT_statements to my script. I'll try on a new document since it probably doesn't make much sense to relate this information with the tables loaded in the previous exercises.

Thanks again!

Best regards,

DataNibbler

[hic]

Good luck!

If you want to read a little about QlikView data modelling and joining tables, I suggest To Join or not to Join.

HIC

[datanibbler]

Thanks!

Reading up is never wrong since in the medium run I'm going to be mainly responsible for everything concerning QlikView and the QV rollout in the company.

After assembling another post here about the load_script not doing exactly what I'd expect it to, I have found the solution. Writing down problems is a great way to get your brain going. In my previous jobs, I used it countless times and quite often I didn't have to actually post my question because I had found the solution already.

One question that remains: I have selected the exact same fields from the employees and the customers table. How then can I keep Qlikview from automatically concatenating those into one table?

Thanks again!

Best regards,

DataNibbler

[hic]

Ever heard of the "Feynman Problem Solving Algorithm"? It's

1. Write down the problem.
2. Think very hard.
3. Write down the answer.

I firmly believe that this works. Forcing yourself to formulate the problem in words is the key to solving the problem. Sometimes you can even skip step two... Feynman got the Nobel Prize.

About QlikView: You need to rename these fields. Either by using "as" in the SELECT or in a Preceding Load (Cumbersome, but clearer and easier to manage). Or by using Qualify (Lazy man's solution - see my script above).

HIC

[datanibbler]

Hi,

OK - I did it with the 'as' option for starters, I just included the ID_field and renamed it 'Cust ID' and 'Emp ID', respectively. More tricks and stuff will come later. I'm happy when it works. Since I just did an exercise with the 'concatenate' option, I also thought of using 'noConcatenate'  - that results in a rather weird-looking table structure with the two tables being displayed as distinct, together with a third table combining both.

Can you tell me what that is?

Thanks a lot!

Best regards,

DataNibbler

[hic · Accepted solution]

I guess you look at the table viewer and what you see is similar to this:

This is the internal view - how QlikVIew has resolved the link between the tables by introducing a Synthetic table. You get this every time there are several keys between the tables.

Now, switch to "Source table view" (at the top in the Table Viewer). Then you will instead see something like the following:

This is the data model that you have created in the script. The keys in the picture should not be there. Hence, remove them by renaming them.

Your data is probably slightly different from mine, so you probably have other keys than I. The bottom line is the same though: Remove the keys you dont want (by naming them differently in the two tables) and add keys that you want (by naming them the same in the two tables.)

HIC

[datanibbler]

Perfect! That switching option to Source_table_view was just what I was looking for.

I'll try to rename the fields so as to remove the remaining links so that I can see who is an employee and who is a customer right away.

I was able to rename a field entry in one of the files I have from earlier exercises ("Market.tab"), so that now I can select a market and all the customers and employees are automatically filtered.

Thanks a lot!

Best regards,

DataNibbler