topic Re: How to use List more efficiently in For Each...Next? in QlikView

How to use List more efficiently in For Each...Next?

Anonymous — Wed, 15 Jan 2014 22:22:21 GMT

Hello,

I need to use For Each loop to run query on different schema in the same database. All the schema have the same tables with different data. Below is a sample query:

For Each vStore in 's10', 's11', 's12', 's13','s14', 's15', 's16','s17','s18', 's19'

accounts:

LOAD *;

SQL

SELECT companyid, firstname, lastname, custid

FROM $(vStore).customer as a

WHERE date(postdate) = '2011-12-31';

NEXT vStore

In the Code above it just shows 10 schema in the list (after 'in') however, I have 40 schema to run the query on. The editor forces me to add schema in the list in the same line. Is it possible to break the line in the list? when I type the stores in the next line it shows syntax error. Keeping list of 40 schema in the same line makes it difficult to read and edit the script. Any Ideas?

Re: How to use List more efficiently in For Each...Next?

Colin-Albert — Wed, 15 Jan 2014 22:36:55 GMT

The load concat function can create a table with a single row containing a csv list of store ids Peek this data into a variable, then use this variable as the stores in your for..each loop.

Re: How to use List more efficiently in For Each...Next?

iktrayanov — Wed, 15 Jan 2014 22:46:51 GMT

Try this

For schema=1 to 50

SQL

SELECT companyid, firstname, lastname, custid

FROM 's$(schema).customer' as a

WHERE date(postdate) = '2011-12-31';

AutoGenerate(1);

next;

Re: How to use List more efficiently in For Each...Next?

maxgro — Wed, 15 Jan 2014 22:46:56 GMT

you can try this

For Each v in 's1', 's2', 's3', 's4'

For Each vStore in '$(v)0', '$(v)1', '$(v)2', '$(v)3', // .......... '$(v)9'

trace $(vStore);

// ......

NEXT vStore

NEXT v

Re: How to use List more efficiently in For Each...Next?

Clever_Anjos — Wed, 15 Jan 2014 23:13:50 GMT

Stores:
LOAD * INLINE [
    F1
    s1
    s2
    s3
...

    s50
];
for i = 0 to NoOfRows('Stores') - 1
  let vStore=Peek('F1',i,'Stores');
  accounts:
  LOAD *;
  SQL
  SELECT companyid, firstname, lastname, custid
  FROM $(vStore).customer as a
  WHERE date(postdate) = '2011-12-31';
NEXT vStore

Re: How to use List more efficiently in For Each...Next?

stevelord — Thu, 27 Feb 2014 23:38:16 GMT

Hi, do you have a version of this for the variable being in the Where part of the statement instead of the From part of the statement?

I have clients in a client account names field of a file, and I need a script to load itself for one client at a time, then the next, and the next sequentially rather than try to load them all from the file simultaneously.

Re: How to use List more efficiently in For Each...Next?

rwunderlich — Fri, 28 Feb 2014 00:06:52 GMT

The $() variables will get substituted anywhere in the statement. So if you load up a table of ClientId, and peek() them into a variable named "vClient", your WHERE clause can look like this.

WHERE ClientId='$(vClient)'

-Rob

Re: How to use List more efficiently in For Each...Next?

stevelord — Fri, 28 Feb 2014 01:25:56 GMT

Thanks, can you assume I’m an idiot and need help on the rest of that syntax? ☺

Say I have these ClientIds: BiffCo, MartyCo, and DocCo

And I have this script:

Load UserId,

ClientId,

Sum(SomeRecords) as PivotedRecords

From C:\Documents\Biometrics.qvd (qvd)

Where ClientId=’$(vClient)’

Group by UserId, ClientId

Can you put the table, peek, and the for each/next stuff precisely where they’d go around that script? I’ve been banging my head on it most of the day and pretty sure I’ve just got some line in the wrong order or the wrong symbol or missing symbol somewhere or other. Rather than share my whole mess, hoping to see a simple clean piece of script that would work independent of anything else. ☺

Re: Re: How to use List more efficiently in For Each...Next?

rwunderlich — Fri, 28 Feb 2014 17:44:21 GMT

Here's a specific example.

// Load a list of ClientIds from somewhere

ClientTable:

LOAD * INLINE [

aClient

BiffCo

MartyCo

DocCo

];

FOR i = 0 to NoOfRows('ClientTable')-1

LET vClient = peek('aClient',$(i), 'ClientTable');

Load UserId,

ClientId,

Sum(SomeRecords) as PivotedRecords

From C:\Documents\Biometrics.qvd (qvd)

Where ClientId=’$(vClient)’

Group by UserId, ClientId

;

NEXT i

Before we get too far afield, I'm wondering why you need to load one client at a time? I just want to understand if we're discussing the best solution.

-Rob

Re: How to use List more efficiently in For Each...Next?

Anonymous — Thu, 06 Mar 2014 16:26:20 GMT

When I got it right:

instead of:

LET vClient = peek('ClientTable', $(i), 'aClient');

it should be:

LET vClient = peek('aClient', $(i), 'ClientTable');

Then it works well. Thanks.

Re: Re: How to use List more efficiently in For Each...Next?

rwunderlich — Thu, 06 Mar 2014 19:38:48 GMT

Thanks for pointing out my mistake. I've corrected the post.

Also added the "-1" to NoOfRows()

-Rob