Use previous loaded column

[#1]

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.

[swuehl]

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

[#3]

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]

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]

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]

Hi,

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

Greetings from Munich

Martina

EAVCO GmbH

[#7]

Thanks Stefan, that made the script more readable.

BTW, why Autogererate 1 after each line?

[#8]

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

[swuehl]

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

[#10]

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