topic Re: Using variables in FROM clause in QlikView

Using variables in FROM clause

olivetwist — Tue, 15 Sep 2015 20:40:07 GMT

I am trying to loop through a list of users and pull Web Service data for each.

I have a list of IDs in a table. Field name is "UserID"

I am trying to do something like this:

FOR Each LnID in UserID

Concatenate Users:

Load *

FROM [http://hrapps//humanresources/hrwebservice/emplogin.asmx/Search?LanID= $(LnID)] (XmlSimple, Table is [DataSet]);

is this possible? any advise from pulling all data from ASMX is also appreciated.

Re: Using variables in FROM clause

sasiparupudi1 — Wed, 16 Sep 2015 08:36:19 GMT

try like this

Users:

FOR i=0 to NoOfRows('yourtable');-1

LET vUserID=trim(Peek('UserID',$(i),'yourtable'));

if i>0 then

Concatenate Users;

end if;

Load *

FROM [http://hrapps//humanresources/hrwebservice/emplogin.asmx/Search?LanID= '$(vUserID')] (XmlSimple, Table is [DataSet]);

next;

hth

Sasi

Re: Using variables in FROM clause

marcus_sommer — Wed, 16 Sep 2015 09:00:20 GMT

You could loop through the table which contained your UserID like SasiParupudi1 had suggested - also possible might be a loop through the field per fieldvalue() which is general faster but you will need bigger datasets to notice the differences.

If you want use FOR Each LnID in UserID the UserID needs to be a variable which contains a list like:

'User1', 'User2', 'User3'

and which could be used like: FOR Each LnID in $(UserID)

Such a listing could be created per concat() in a previous load and then per peek() assigned to a variable.

Therefore several ways are possible and the easiest way seems to be to loop through a table but if there are further requirements this could be change.

- Marcus