topic Re: Do while with variable in QlikView

Do while with variable

kris_vliegen — Tue, 20 May 2014 12:01:38 GMT

Hello all,

I'm trying to load data from an Iseries.

And every bookyear is in another Company and also an other filepath(accesspath)

I have an excel with Year and CompanyNumber.

Boekjaar, BoekLib

2011 11E

2012 12E

2013 13E

2014 14E

I have only need to refresh the last 2 years (current year and CurrentYear -1)

Now I need to do a select like this

select *

FROM S0678ADT.X62FIL13EO.PBP;

and

Select *

FROM S0678ADT.X62FIL14EO.PBP;

Here is an example of my script where my BoekLib (in Yellow) is empty...

How can I fill up "BoekLib"?

Regards,

Kris

Re: Do while with variable

marcus_sommer — Tue, 20 May 2014 12:06:14 GMT

You could use peek('BoekLib', $(vFileNo), 'Lib') to run and read your Lib-Table.

- Marcus

Re: Do while with variable

alexandros17 — Tue, 20 May 2014 12:07:31 GMT

After your FOR iinstruction write:

LET TMP = Peek('BoekLib',$(vFileNo)-1,'Lib'');

then replace your yellow BoekLib with $(TMP)

Let me know ...

Re: Do while with variable

kris_vliegen — Tue, 20 May 2014 12:15:33 GMT

Thanks for te response.

I've made this script but I get an error (see below)

Error:

Re: Do while with variable

marcus_sommer — Tue, 20 May 2014 12:19:54 GMT

$(TMP) is a string and need to write with quotes: '$(TMP)'.

- Marcus

Re: Do while with variable

alexandros17 — Tue, 20 May 2014 12:25:11 GMT

LET vFileName = 'S0678ADT.X62FIL$(TMP)=.PBP'

Re: Do while with variable

kris_vliegen — Tue, 20 May 2014 13:01:42 GMT

Thanks!

But I was expecting he was only giving the yellow records...

Re: Do while with variable

marcus_sommer — Tue, 20 May 2014 13:32:59 GMT

It seems that in your Lib-Table are more/other values as you expect - perhaps you need to extend your where-clause.

- Marcus

Re: Do while with variable

kris_vliegen — Tue, 20 May 2014 14:06:54 GMT

It's working fine. I was linking some data in the table that wasn't matching.

Re: Do while with variable

kris_vliegen — Thu, 22 May 2014 10:21:59 GMT

Hi All,

Everything was working fine.

But if I try to use it for another file I get some strange things...

Fot the file PBD he is filling one table "PBD

If I do the same for the PBP file he is making 2 tables PBP and PBP-1

Why is he doing this

And this is the excel I'm using for both the scripts

Re: Do while with variable

marcus_sommer — Thu, 22 May 2014 14:10:35 GMT

You used for your load within the loop the functionality that qv automatically concatenated tables which have the same fields. Is there any difference between the tables qv create a new table with a number-suffix.

In this case you should check your data and if they are correct you need a forced concatenate:

for vFileNo ...

let vConcatenate = if($(vFileNo) = 1, '', 'concatenate(MyTable)');

....

MyTable:

$(vConcatenate)

Load ....

SQL ....

- Marcus

Re: Do while with variable

kris_vliegen — Thu, 22 May 2014 14:21:21 GMT

Marcus,

thanks for you respond.

As I go and check the files there is indeed a difference between the files.

I had also found a solution that was working.

Lib:

LOAD

Boekjaar,

BoekLib

FROM

[..\01 Table Files\Boekhoudjaren.xlsx]

(ooxml, embedded labels, table is Blad1) ;

PBPTemp:

load * inline [

Temp

];

For vFileNo = 1 to NoOfRows('Lib')

LET TMP = Peek('BoekLib',$(vFileNo)-1,'Lib');

LET vYear = Peek('Boekjaar',$(vFileNo)-1,'Lib');

Let vFileName = 'S0678ADT.X62FIL' & '$(TMP)' & 'O.PBP';

Concatenate(PBPTemp)

PBPTemp:

Load *,

'$(vYear)' as Year;

SQL SELECT *

FROM $(vFileName) ;

Next vFileNo

Drop Table Lib;

NoConcatenate

PBP:

LOAD

BPKODE,

BPINTR,

BPHOVE,

BPFILL,

Year

Resident PBPTemp where Temp <> 1 ;

Drop Table PBPTemp ;