11 Replies Latest reply: Dec 22, 2011 12:04 PM by Thomas Ljungström RSS

    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.

        • Use previous loaded column
          Stefan Wühl

          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

              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
                  Goran Korsgren

                  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.

                  • Re: Use previous loaded column
                    Stefan Wühl

                    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

                      • Use previous loaded column
                        Martina Brenner

                        Hi,

                         

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

                         

                        Greetings from Munich

                        Martina

                        EAVCO GmbH

                          • Re: Use previous loaded column

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

                              • Re: Use previous loaded column
                                Stefan Wühl

                                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

                                    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
                                        Stefan Wühl

                                        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

                                • Re: Use previous loaded column

                                  Thanks Stefan, that made the script more readable.

                                  BTW, why Autogererate 1 after each line?