Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
dirk1955
Contributor

SQL problem

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$);

Tags (1)
1 Solution

Accepted Solutions
nstefaniuk
Contributor III

Re: SQL problem

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,

KD

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.

7 Replies
dirk1955
Contributor

Re: SQL problem

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

nstefaniuk
Contributor III

Re: SQL problem

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,

KD

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

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

dirk1955
Contributor

Re: SQL problem

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

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

dirk1955
Contributor

Re: SQL problem

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

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

Community Browser