topic Nested SELECT/LOAD in QlikView

Nested SELECT/LOAD

Mon, 23 Apr 2012 10:01:19 GMT

Hi,

I have the following situation:

An SQL table PROJECT which contains the following columns:

ID	UserName1	UserName2	UserName3
1	john	paul	sean
2	pierre	paul	jacques
3	john	simon	sean

An Excel file TEAM which contains the following columns:

UserName	UserTeam
john	Team1
paul	Team2
sean	Team3
pierre	Team1
jacques	Team4
simon	Team5

The result table that should look like this:

ID	UserName1	UserTeam1	UserName2	UserTeam2	UserName3	UserTeam3
1	john	Team1	paul	Team2	sean	Team3
2	pierre	Team2	paul	Team2	jacques	Team4
3	john	Team1	simon	Team5	sean	Team3

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?

Re: Nested SELECT/LOAD

lironbaram — Mon, 23 Apr 2012 10:10:19 GMT

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

Re: Nested SELECT/LOAD

stevedark — Mon, 23 Apr 2012 10:20:40 GMT

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/

Nested SELECT/LOAD

Mon, 23 Apr 2012 10:38:51 GMT

@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

Nested SELECT/LOAD

lironbaram — Mon, 23 Apr 2012 10:56:11 GMT

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

Nested SELECT/LOAD

stevedark — Mon, 23 Apr 2012 11:11:18 GMT

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.

Nested SELECT/LOAD

Mon, 23 Apr 2012 11:28:02 GMT

Apologies Liron, your proposal works perfectly...!

Nested SELECT/LOAD

Mon, 23 Apr 2012 11:29:21 GMT

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