Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Nested SELECT/LOAD

Hi,

I have the following situation:

An SQL table PROJECT which contains the following columns:

IDUserName1UserName2UserName3
1johnpaulsean
2pierrepauljacques
3johnsimonsean

An Excel file TEAM which contains the following columns:

UserNameUserTeam
johnTeam1
paulTeam2
seanTeam3
pierreTeam1
jacquesTeam4
simonTeam5

The result table that should look like this:

IDUserName1UserTeam1UserName2UserTeam2UserName3UserTeam3
1johnTeam1paulTeam2seanTeam3
2pierreTeam2paulTeam2jacquesTeam4
3johnTeam1simonTeam5seanTeam3

With two SQL tables, I would do something like:

SELECT

UserName1,

UserTeam1,

UserName2,

UserTeam2,

UserName3,

UserTeam3

FROM PROJECT

LEFT OUTER JOIN(

SELECT UserTeam1

FROM TEAM) ON UserName1 = UserName

LEFT OUTER JOIN(

SELECT UserTeam2

FROM TEAM) ON UserName2 = UserName

LEFT OUTER JOIN(

SELECT UserTeam3

FROM TEAM) ON UserName3 = UserName

Is it possible to do the same but instead of nested SELECT, data would be loaded from the Excel file?

1 Solution

Accepted Solutions
lironbaram
Partner - Master III
Partner - Master III

there is no mix first you load form the sql the you left join data from excel

i do it all the time can you post the script you wrote

View solution in original post

7 Replies
lironbaram
Partner - Master III
Partner - Master III

you can dosomething in the lines of

SELECT

UserName1,

UserName2,

UserName3,

FROM PROJECT

LEFT join

load UserName as UserName1

UserTeam as UserTeam1

FROM xlsfile

LEFT join

load UserName as UserName2

UserTeam as UserTeam2

FROM xlsfile

LEFT join

load UserName as UserName3

UserTeam as UserTeam3

FROM xlsfile

stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi there,

I would suggest that the best way to approach this would be to load the Excel table into a QlikView mapping table and do an ApplyMap for each of the three UserNames.  I've written a blog posting on ApplyMap here: http://bit.ly/m58kwC

Also, might I suggest that you look at the CROSSTAB statement, so you end up with just one column of names and teams.  If the location of the UserName (ie. 1, 2 or 3) is important you can make this a dimension of the Cross Table.

Hope that helps.

Steve

http://www.quickintelligence.co.uk/

Not applicable
Author

@Liron: Unfortunately it does not work, I do not think you can mix qlikview instructions to load data from Excel within an SQL Select

@Steve: I am checking your article

lironbaram
Partner - Master III
Partner - Master III

there is no mix first you load form the sql the you left join data from excel

i do it all the time can you post the script you wrote

stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

Liron - does your code not need some semicolons, after PROJECT and each of the three xlsfiles?  Also slightly more code is required to define the location and format of the xls file.

I would still maintain that ApplyMap would be a much better way of approaching this though.

Not applicable
Author

Apologies Liron, your proposal works perfectly...!

Not applicable
Author

Indeed Liron provided the overall solution and syntax needed to be completed. The ApplyMap is not for mapping values instead of columns?