Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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.

11 Replies
swuehl
MVP
MVP

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
Author

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
Specialist II
Specialist II

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
MVP
MVP

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
Partner - Specialist II
Partner - Specialist II

Hi,

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

Greetings from Munich

Martina

EAVCO GmbH

Not applicable
Author

Thanks Stefan, that made the script more readable.

BTW, why Autogererate 1 after each line?

Not applicable
Author

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

swuehl
MVP
MVP

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
Author

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