topic Use previous loaded column in QlikView

Use previous loaded column

Wed, 30 Nov 2011 19:12:30 GMT

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.

Use previous loaded column

swuehl — Wed, 30 Nov 2011 23:53:13 GMT

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

Re: Use previous loaded column

Wed, 21 Dec 2011 12:38:08 GMT

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

Use previous loaded column

gandalfgray — Wed, 21 Dec 2011 13:05:39 GMT

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;

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.

Re: Use previous loaded column

swuehl — Wed, 21 Dec 2011 14:59:34 GMT

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

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;

Hope this helps,

Stefan

Use previous loaded column

brenner_martina — Wed, 21 Dec 2011 15:01:22 GMT

Hi,

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

Greetings from Munich

Martina

EAVCO GmbH

Re: Use previous loaded column

Thu, 22 Dec 2011 08:28:53 GMT

Thanks Stefan, that made the script more readable.

BTW, why Autogererate 1 after each line?

Re: Use previous loaded column

Thu, 22 Dec 2011 10:37:15 GMT

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

Re: Use previous loaded column

swuehl — Thu, 22 Dec 2011 12:04:04 GMT

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

Re: Use previous loaded column

Thu, 22 Dec 2011 15:03:28 GMT

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

Use previous loaded column

swuehl — Thu, 22 Dec 2011 16:00:42 GMT

Thomas,

I would first try to split your pString into appropriate fields. Honestly, I don't know what these fields would be. I had a short look at the web sites and try to figure out how we could link the tables based on what information pieces, but no luck up to now.

I remember that you posted a question about splitting up something that looked like pString a few weeks ago, have you succeeded to apply your solution from that post to this application?

Could you tell us at what piece of information we need to look? What is making up a team / player? Or what would you like to analyze in detail?

I am leaving office now, not sure if I have the chance to work on that for the next days, but I think any information might help also others to help you.

Regards,

Stefan

Use previous loaded column

Thu, 22 Dec 2011 17:04:37 GMT

Hi,

in the data there are no way of linking. Maybe if we could make Date a variable from Games and reuse that in Player (together with Season). Splitting of the the string is in progress.