Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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
Honored Contributor II

Nested SELECT/LOAD

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

7 Replies
lironbaram
Honored Contributor II

Re: Nested SELECT/LOAD

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

MVP
MVP

Re: Nested SELECT/LOAD

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

Nested SELECT/LOAD

@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
Honored Contributor II

Nested SELECT/LOAD

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

MVP
MVP

Nested SELECT/LOAD

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

Nested SELECT/LOAD

Apologies Liron, your proposal works perfectly...!

Not applicable

Nested SELECT/LOAD

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

Community Browser