topic Re: Typical Qlikview Program Structure in QlikView

Typical Qlikview Program Structure

Anonymous — Tue, 02 Jul 2013 19:17:16 GMT

Hello my friends. I am fairly new to QlikView and am really struggling with some of the basic concepts of the program. My background is in Access to some of the QV syntax is very different to me. For example, in Access, I could tell someone just starting out that the way to create a db app is to connect to an ODBC, link to specific tables, query those tables and then generate reports off of said queries. In QV this does not seem to be the case. I have successfully connected to my ODBC and brought in four different tables. I have qualified each table to make it easier (I thought) to then join certain fields together. This has proved incredibly frustrating for me. I keep getting synthetic keys and have been told that they are a bad thing. Please, would someone take the time to break down for me how you would go about structuring an easy program in QV.

Example:
 
 01.  CONNECT to ODBC_y;
 02.  QUALIFY *;
 03.  tblName_A:
 04.  SELECT *
 05.  FROM tblName_A
 06.  WHERE condition;
 07.  UNQUALIFY *;
 08.
 09.  CONNECT to ODBC_z;
 10.  QUALIFY *;
 11.  tblName_B:
 12.  SELECT *
 13.  FROM tblName_B
 14.  WHERE condition;
 15.  UNQUALIFY *;
 
 Now how do I merge these two tables together into one?

Re: Typical Qlikview Program Structure

jagan — Tue, 02 Jul 2013 19:21:55 GMT

Hi,

Try like this

CONNECT to ODBC_y;

tblName_A:

SELECT *

FROM tblName_A

WHERE condition;

CONNECT to ODBC_z;

Concatenate(tblName_A)

SELECT *

FROM tblName_B

WHERE condition;

Hope this helps you.

Regards,

Jagan.

Re: Typical Qlikview Program Structure

Anonymous — Tue, 02 Jul 2013 19:27:41 GMT

Thank you so much for the quick response Jagan. Unfortunately I “think” what I am trying to do is an inner join. My first example was vague. Basically the first section of code was bringing in two tables. Now that I have two tables, they each have a field (for example, tblName_A.him in the first table and tblName_B.her in the second) that I need to join on.I am not sue of the syntax to make this join with two fields from different tables that have different names but the same data in them…

Re: Typical Qlikview Program Structure

jagan — Tue, 02 Jul 2013 20:15:21 GMT

CONNECT to ODBC_y;

tblName_A:

SELECT *

FROM tblName_A

WHERE condition;

CONNECT to ODBC_z;

INNER JOIN(tblName_A)

SELECT

her as him,

FROM tblName_B

WHERE condition;

If columns are same then qlikview will join the tables.

Regards,

Jagan.

Re: Typical Qlikview Program Structure

Anonymous — Tue, 02 Jul 2013 20:23:55 GMT

What if the columns are not the same?

Re: Typical Qlikview Program Structure

jagan — Wed, 03 Jul 2013 06:59:07 GMT

Hi,

If the column names are not same then you have to rename the columns by using AS, Qlikview automatically joins if column names are same in the tables.

Regards,

Jagan.

Re: Typical Qlikview Program Structure

flipside — Wed, 03 Jul 2013 08:21:36 GMT

Hi thnkgreen,

Qlikview can be a little odd when first starting out, but your experience in Access will help you learn quickly. Essentially what you are trying to do in QV is denormalise the data model to a star schema instead of separate tables you would find in a traditional RDBMS. QV handles the storage of duplicated data for you, and less tables in memory will generally perform quicker.

I would avoid Select * statements because you could lose control of your QV datamodel (eg. if a new field is added to your datasource which has a field name existing elsewhere they will create a possibly unwanted join). I highly recommend the free online courses and buying a good book - Qlikview11 for Developers by Miguel Garcia and Barry Harmsen is highly recommended, but there are others. I've been using QV for 7 years but still picked up more knowledge than I expected from these publications.

flipside