SQL Subselects

[#1]

I have two tables, one of which is a data table and the other an index table:

Data

Title	Description	Created By
Alpha	aaaaaaaaaaaaaaaa	A1
Beta	bbbbbbbbbbbbbbbbb	A2
Gamma	cccccccccccccccc	B1
Delta	dddddddddddddddd	A1
Epsilon	eeeeeeeeeeeeeee	C1

Index

ID	First Name	Last Name	Code
A1	Albert	Smith	ABC123
A2	Jenny	Jones	DEF123
B1	Sam	Williams	XYZ999
B2	Karla	Tate	HIJ000
C1	John	Doe	LMN987

I want to load the Data table into Qlikview using the "Created By" field to link to the Index table so I can concatenate the "First Name" and "Last Name" fields based on a matching "Created By" value and "ID" value. I want the resulted concatenation stored in "User ID."

LOAD

    *,

    Concat(`Created By Last Name`,' ',`Created By First Name`) As `User ID`;

SQL SELECT

     Title,

     Description,

    (SELECT `Last name` FROM Index i WHERE i.ID=[d.Created By]) As `Created By Last Name`,

    (SELECT `First name` FROM Index i WHERE i.ID=[d.Created By]) As `Created By First Name`

FROM `Data` d;

This fails with an ODBC error (no descriptive text). What is missing?

[flipside]

Hi throwingmuse,

The Concat line is the error here as it doesn't work in the way it does in Excel. In Qlikview it will iterate over all values when used with a group by clause.  All you need to do for your requirement is combine using '&' ...

LOAD

    *,

    `Created By Last Name` & ' ' & `Created By First Name` As `User ID`;

flipside