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?
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`;
