CODEWITHSUNDEEP
subquerymappingcaseqlikview
Matt
Matt

Reputation: 15071

Qlikview CASE statement or subquery inside a load and apply map

There is an inline load i have to add Song ID's to an exempt list:

Exemptsongs:

Load * Inline [
Song ID
45875
65463
43785
90347
23456
89438
16745
];

Exemptsongsmap:
Mapping LOAD
[Song ID],'Exempt' as [Exempt songs exempt]
Resident Exemptsongs;

DROP Table Exemptsongs;

This is then loaded in elsewhere in a playlist table when needed.

What i want to do is also add song ids that have been added by a specific user to be exempt, but only when added by that user, they are not exempt when added by anyone else (unless there specifically in the exempt list above).

I was thinking(pseudo) something like this:

Exemptsongs:

Load * Inline [
Song ID
45875
65463
43785
90347
23456
89438
16745
(SELECT SongID FROM Songs WHERE addedbyuser = 'MATT')
];

Exemptsongsmap:
Mapping LOAD
[Song ID],'Exempt' as [Exempt songs exempt]
Resident Exemptsongs;

DROP Table Exemptsongs;

Then when loading the playlist the applymap part is currently this:

ApplyMap('Exemptsongsmap',[Playlist ID],'Non-exempt') as [Exempt songs exempt],

But cannot think how to not allow the songid to be exempt if it was added by someone else also.

e.g.

if user matt adds the song id 12345 and another user adds it i want it to be exempt in playlists when it was added by matt.

Error 1:

Syntax error, missing/misplaced FROM:

Exemptsongs:

Load [Song ID]& as UniqueID Inline [
Song ID
45875
65463
43785
90347
23456
89438
16745
]
Exemptsongs:

Load [Song ID]& as UniqueID Inline [
Song ID
45875
65463
43785
90347
23456
89438
16745
]

Error 2:

Field not found - <Song ID>
load [Song ID]&addedbyuser as UniqueID 
from Songs 
where addedbyuser='Matt';

Error 3:

Table not found
Exemptsongsmap:
Mapping LOAD Distinct
UniqueID,'Exempt' as [Exempt songs exempt]
Resident Exemptsongs

Error 4:

Table not found
DROP TABLES statement

Then multiple other errors with things we haven't changed...

Upvotes: 0

Views: 2259

Answers (1)

The Budac
The Budac

Reputation: 1633

You need to create a unique Id that is the SongId and the addedbyuser concatenated. The chr(39) stuff is just to create a list that has each value as 'value'.

USERS:
load concat(distinct addedbyuser,chr(39)&','&chr(39)) as userids from Songs;

let vList = chr(39)&fieldvalue('userids',1)&chr(39);;

for each a in $(vList)

ExemptSongs:
Load [Song ID]&$(a) as UniqueID; 
Inline [
Song ID
45875
65463
43785
90347
23456
89438
16745
];

next a

//Allow auto concatenate of these tables
load [Song ID]&addedbyuser as UniqueID 
from Songs 
where addedbyuser='Matt';

Exemptsongsmap:
Mapping LOAD distinct
UniqueID,'Exempt' as [Exempt songs exempt]
Resident Exemptsongs;

DROP Table Exemptsongs;

And then lastly

ApplyMap('Exemptsongsmap',[Playlist ID]&addedbyuser,'Non-exempt') as [Exempt songs exempt],

Upvotes: 1

Related Questions