topic Re: SQL problem in QlikView

SQL problem

dirk1955 — Thu, 19 Jan 2012 22:27:18 GMT

Hi,

In qlikview i have 2 tables:

DataA208:
LOAD Jaar,
     IDF,
     RX,
     CX,
     VV,
     KD,
     Doc,
     TCT,
     [Code station]
FROM
[..\..\CV-R-KD\A208\A208.xls]
(biff, embedded labels, table is Sheet1$);Next i want to run a SQL on those tables:

Then i want to run a SQL on those tables.

The purpose, joining the data from both tables.

LOAD;

SQL

SELECT RESIDENT DataA208.Jaar, RESIDENT DataA208.IDF, RESIDENT DataA208.RX, RESIDENT DataA208.CX, RESIDENT DataA208.VV, RESIDENT DataA208.KD, RESIDENT [Code stations MPP].StationRap

FROM RESIDENT DataA208 LEFT JOIN RESIDENT DataMPPCodeStation ON RESIDENT DataA208.[Code station] = RESIDENT DataMPPCodeStation.[Code Station];

Then i get the error, syntax error,missing operator in querry expression.

What am I doing wrong?

I'm pretty new in Qlikview

DataTest208:

DataMPPCodeStation:
LOAD [Code Station], StationRap FROM
[..\..\Code stations MPP.xls]
(biff, embedded labels, table is Sheet1$);

Re: SQL problem

dirk1955 — Thu, 19 Jan 2012 22:33:13 GMT

Modified question, my text was messed up.

Hi,

In qlikview i have 2 tables:

DataMPPCodeStation:

LOAD [Code Station], StationRap FROM
[..\..\Code stations MPP.xls]
(biff, embedded labels, table is Sheet1$);

and

DataA208:
LOAD Jaar,
     IDF,
     RX,
     CX,
     VV,
     KD,
     Doc,
     TCT,
     [Code station]
FROM
[..\..\CV-R-KD\A208\A208.xls]
(biff, embedded labels, table is Sheet1$);

Then i want to run a SQL on those tables.

The purpose, joining the data from both tables.

DATATEST208:

LOAD;

SQL

SELECT RESIDENT DataA208.Jaar, RESIDENT DataA208.IDF, RESIDENT DataA208.RX, RESIDENT DataA208.CX, RESIDENT DataA208.VV, RESIDENT DataA208.KD, RESIDENT [Code stations MPP].StationRap

FROM RESIDENT DataA208 LEFT JOIN RESIDENT DataMPPCodeStation ON RESIDENT DataA208.[Code station] = RESIDENT DataMPPCodeStation.[Code Station];

Then i get the error, syntax error,missing operator in querry expression.

What am I doing wrong?

I'm pretty new in Qlikview

Re: SQL problem

nstefaniuk — Fri, 20 Jan 2012 07:29:21 GMT

Hi.

I have never seen this syntax in fact. Are you sure that you can SQL with resident ? Try this code:

DATATEST208:

LOAD

[Code station],

Jaar,

IDF,

RX,

CX,

VV,

RESIDENT DataA208;

LEFT JOIN (DATATEST208)

Load

[Code station],

StationRap

RESIDENT DataMPPCodeStation;

Remember that the syntax "table.field" can be used in QV only if you have declared QUALIFY before. Else you will just use "field" and the join is done implicitely on the synonyms.

Re: SQL problem

Miguel_Angel_Baeyens — Fri, 20 Jan 2012 08:08:27 GMT

Hi,

nstefaniuk suggestion is correct. However, I'd rather use a mapping table. Depending on the size of the data set, JOINs in QlikView use a lot of disk space making the application bigger. The result, if the data model is correct, will be the same:

DataMPPCodeStationMap:
MAPPING LOAD [Code Station], StationRap FROM
[..\..\Code stations MPP.xls]
(biff, embedded labels, table is Sheet1$);
DataA208:
LOAD Jaar, 
     IDF, 
     RX, 
     CX, 
     VV, 
     KD, 
     Doc, 
     TCT, 
     [Code station],
     ApplyMap('DataMPPCodeStationMap', [Code station]) AS StationRap
FROM
[..\..\CV-R-KD\A208\A208.xls]
(biff, embedded labels, table is Sheet1$);

Note that in QlikView field names and values are case sensitive, so [Code station] is not the same as [Code Station] (note the capital S in the second table).

Hope that helps.

Miguel

Re: SQL problem

dirk1955 — Fri, 20 Jan 2012 10:21:50 GMT

Hi,

"Remember that the syntax "table.field" can be used in QV only if you have declared QUALIFY before. Else you will just use "field" and the join is done implicitely on the synonyms."

How and where do i have to declare "QUALIFY"?

Thx for the answers.

Re: SQL problem

Miguel_Angel_Baeyens — Fri, 20 Jan 2012 10:31:39 GMT

Hi Dirk,

Qualifying prefixes each filed with the name of the table. See this example:

QUALIFY *;
Data:
LOAD Field1,
     Field2
FROM File.qvd (qvd);

Will create a table "Data" with fields "Data.Field1" and "Data.Field2" instead of the normal "Field1" and "Field2". As opposed to traditional SQL, where many tables may share many field names, and you need to specify which field does the relationship, QlikView links automatically tables when one or more fields are named the same.

Hope that makes sense.

Miguel

Re: SQL problem

dirk1955 — Sat, 21 Jan 2012 15:14:56 GMT

Hi,

“and you need to specify which field does the relationship”, how should I do that?

Sorry, newbie need to learn a lot.

Thx

Dirk

Van: Miguel Angel Baeyens

Verzonden: vrijdag 20 januari 2012 11:32

Aan: Dirk De Wispelaere

Onderwerp: - Re: SQL problem

<http://community.qlik.com/index.jspa> QlikCommunity

Re: SQL problem

created by Miguel Angel Baeyens <http://community.qlik.com/people/mabaeyens> in Development (QlikView Desktop) - View the full discussion <http://community.qlik.com/message/184174#184174

Re: SQL problem

Miguel_Angel_Baeyens — Sun, 22 Jan 2012 10:57:29 GMT

Hi Dirk,

I was talking about SQL, not QlikView. In SQL you need to specify the key fields in each table, irrespective their name. Not in QlikView, where two tables links were the name of at least one field is the same.

Regards.

Miguel