topic Re: Variable in a where statement? in QlikView

Variable in a where statement?

stevelord — Mon, 30 Mar 2015 19:54:11 GMT

Hi, I have a vague idea that I'd like to include a variable that refers to a list of company names in my various load scripts' where statements. This would make the script easier to maintain overall. Does anyone have an example qvw or something they can share which does this? Script below is wrong I'm sure, just a vague illustration of the concept I'm thinking of.

//some script that sets up a list of values to be included in the variable//

Let vClients = match(ClientName, 'Acme', 'ABC', '123', 'S-Mart')

//then some load script like below maybe//

Users:

Load UserId,

//ClientName,

OfficeName

FROM Data\Users.qvd where match(ClientName, $(vClients));

Registration:

Load UserId,

//ClientName,

RegistrationDate

FROM Data\Registrations.qvd where match(ClientName, $(vClients));

Attendance:

Load UserId,

//ClientName,

AttendanceDate

FROM Data\Attendance.qvd where match(ClientName, $(vClients));

Thanks in advance!

Re: Variable in a where statement?

Anonymous — Mon, 30 Mar 2015 20:13:18 GMT

The whole condition is already in the variable, but it should be SET, not LET

SET vClients = match(ClientName, 'Acme', 'ABC', '123', 'S-Mart');

...

...Where $(vClients);

Re: Variable in a where statement?

maxgro — Mon, 30 Mar 2015 20:15:02 GMT

an example

let Where = 'where match(ClientName, ' & chr(39) & 'A' & chr(39) & ',' & chr(39) & 'B' & chr(39) & ')';

tmp:

load * inline [

ClientName

];

final:

NoConcatenate

load *

Resident tmp

$(Where)

;

DROP Table tmp;

Re: Variable in a where statement?

Mon, 30 Mar 2015 20:17:31 GMT

Hi,

I use variables in where clause especially with dates when reading SQL -> QVD. I don't see why it wouldn't work with match so go ahead and experiment.

quick example:

SET vDate = 1.1.2015;

full:

LOAD id,

Date

FROM

[full.xlsx]

(ooxml, embedded labels, table is Sheet1) WHERE Date < '$(vDate)' ;