Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

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.
Not applicable

ODBC field name/table join

Hi,

I want to use the following in qlikview with an odbc link to our iSeries database.

sql SELECT DDWEEK, sum(DDTOTL) FROM ls3dta100a/ddp GROUP BY DDWEEK;

but this doesn't create join to another table with a field called[WEEK NO], so I tried:

load DDWEEKas [WEEK NO],

DDTOTL as [TOTAL DESPATCHED];

sql SELECT [WEEK NO], sum([TOTAL AS DESPATCHED]) FROM ls3dta100a/ddp GROUP BY [WEEK NO];

But this creates a ODBC error when connecting to the table on the iSeries. How can I achieve the renaming of the fields so that I can get a join?

Many thanks in advance

Shaun

1 Solution

Accepted Solutions
Not applicable

Re: ODBC field name/table join

Try this:

Load DDWEEK as [WEEK NO],

        TOTAL;

SQL Select DDWEEK, sum(DDTOTL) as TOTAL

     FROM ls3dta100a/ddp

     GROUP BY DDWEEK;

5 Replies
Not applicable

Re: ODBC field name/table join

load is ok but sql select should be withous "sum" expression:

sql SELECT [WEEK NO], [TOTAL AS DESPATCHED] FROM ls3dta100a/ddp GROUP BY [WEEK NO];

Not applicable

Re: ODBC field name/table join

Thanks, but using the brackets on ODBC for the iSeries throws up an error

SQL##f - SqlState: 37000, ErrorCode: 4294967192, ErrorMsg: [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0104 - Token [ was not valid. Valid tokens: ( + * - ? : DAY INF NAN RID ROW RRN CASE CAST CHAR DATE DAYS HASH.

sql SELECT [WEEK NO], [TOTAL AS DESPATCHED] FROM ls3dta100a/ddp GROUP BY [WEEK NO]

Not applicable

Re: ODBC field name/table join

how big is you database?

can't you write sql select * ?

Not applicable

Re: ODBC field name/table join

Try this:

Load DDWEEK as [WEEK NO],

        TOTAL;

SQL Select DDWEEK, sum(DDTOTL) as TOTAL

     FROM ls3dta100a/ddp

     GROUP BY DDWEEK;

Not applicable

Re: ODBC field name/table join

That worked.

Thanks

Community Browser