Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Use previous loaded column

When looping thru LOAD statement can I reuse a column? I need to so I can build a key. Source is on the web so the relation is a click on a link.

Some pseudo code to explain:
start loop 1
LOAD A,B,C
from source1
start loop 2
LOAD A,D,E
from source2
A in loop 2 should come from loop 1 The two load becomes two tables.

Tags (3)
11 Replies
swuehl
Not applicable

Use previous loaded column

Thomas,

I am not sure if I understand, well, no, I am pretty sure i don't.

If you say loop, are you talking about something like a for...next loop?

Then it would be interesting where you place your next statements, i.e. if the second loop is embedded into first or not.

As I understand, column A is not coming from source2, so how is each record of column A related to the records of column D,E? Do you want it to be record number wise?

Then, you could maybe just use a recno() as column in both tables to link the two tables?

Maybe a somewhat more concrete example would be of help (at least for my understanding).

Regards,

Stefan

Not applicable

Re: Use previous loaded column

Hi!

This is how I do it today, but I need to link teams and players. My suggestion were a nested for...loop but I can't get it to work.

for v_season= 1 to 44

IF Match($(v_season), 23, 25, 26, 27, 28, 29) = 0 THEN

teams:

LOAD Date,

     Game,

     Result,

     Result1 as PeriodResult,

     Spectators,

     Venue,

     $(v_season) as Säsong

FROM

[http://domain.com?&qsSeasonId=$(v_season)];

end if

next

set ErrorMode=0;

for season= 30 to 44

for match= 1 to 400

if $(match)<10 then

players:

LOAD @1 as pString,

$(season) as Season,

$(match) as Match

FROM

[http://domain.com/history/gamefacts.asp?qsMatchnr=9000100$(match)&qsSeasonId=$(season)];

elseif $(match)>10 and $(match)<100 then

players:

LOAD @1 as pString,

$(season) as Season,

$(match) as Match

FROM

[http://domain.com/history/gamefacts.asp?qsMatchnr=900010$(match)&qsSeasonId=$(season)];

else

players:

LOAD @1 as pString,

$(season) as Season,

$(match) as Match

FROM

[http://domain.com/history/gamefacts.asp?qsMatchnr=90001$(match)&qsSeasonId=$(season)];;

end if

next

next

gandalfgray
Not applicable

Use previous loaded column

I believe you should use an outer loop for your first table, and use the peek function.

something like this:

LOAD A,B,C

from source1;

Let nMax=NoOfRows('source1')-1;

for n=0 to $(nMax)

     Let vA=peek('A',$(n),'source1');

    

     LOAD $(vA) As A,D,E

     from source2;

next

but to me it seems you need some kind of where condition loading from source2 so you only connect where a connection should really be made.

swuehl
Not applicable

Re: Use previous loaded column

Thomas,

it seems to me that you are only using the if-else statements in your last script part to get the URL query format in your Web file load correct. I think this can be done without the if-else:

Your current code is similar to (I just simulated the query building as a field value):

for match= 1 to 400

if $(match)<10 then

players:

LOAD '9000100$(match)' as pString AutoGenerate 1;

elseif $(match)>=10 and $(match)<100 then

players:

LOAD '900010$(match)' as pString AutoGenerate 1;

else

players:

LOAD '90001$(match)' as pString AutoGenerate 1;

end if

next

And I think the format can be forced by the use of num function:

for match2= 1 to 400

let vMatchText = num($(match2),'000');

players2:

LOAD '90001$(vMatchText)' as pString2

AutoGenerate 1;

next

Hope this helps,

Stefan

brenner_martina
Not applicable

Use previous loaded column

Hi,

you can use the function previous(field) or peek(field)

Greetings from Munich

Martina

EAVCO GmbH

Not applicable

Re: Use previous loaded column

Thanks Stefan, that made the script more readable.

BTW, why Autogererate 1 after each line?

Not applicable

Re: Use previous loaded column

Martina, previous and/or peek don't work since it's not the same table.

swuehl
Not applicable

Re: Use previous loaded column

Thomas, you may use peek() also to retrieve values from a different table (use all 3 parameter):

peek(fieldname [ , row [ , tablename ] ] )

The proposed change to your load seems not to fix your initial issue, right? So could you explain a bit more detailed what you get when running your script and what you expect to get?

I noticed that your two tables teams and players don't share any common field names to link together. I am not sure what is appropriate, so it would be helpful if you could explain what the content of your two tables is and how you would like to link teams with players.

Regards,

Stefan

Not applicable

Re: Use previous loaded column

Stefan, correct - that was not my issue, but it was a good change.

Attaching a qvw with, parts of, the script.

You can follow the links to see the link.

I think I have to store tabel1 (Games) first and then loop through it somehow with a resident load to create the link to table2 (Players). A guess...

regards

thomas