LOOP-problem

[#1]

Hey guys,

I am totally new to QlikView.

I am facing the following scenario:

I have a LOOP (code in the attached *.txt file) that is working fine such as it is.

• Now I have to enhance that: Inside the LOOP, I have to do the following:
  • LOAD data from one table (in a RESIDENT LOAD)
  • Join to it some data from another table (also in a RESIDENT LOAD)
• That should give me just one table with one record per iteration
• Afterwards, I have to use that table to calculate the average of all the records.

• Before (without the JOIN), it worked fine and the table was appended with every iteration
• What do I have to do to get this working?

Thanks a lot in advance!

Best regards,

weatherman

[alexandros17]

when you perform a join inside a for-next each iteration assign a different name to the table so you have to write something like:

for i = 1 to 10

     mytable_$(i):

     load * from t1;

     join

     load * from t2

next

then in another loop:

mydefinitivetable:

load (list all the fields of the join) where 1=0; // this return an empty table with desidered structure

for i = 1 to 10:

     mydefinitivetable:

     load * resident mytable_$(i);

     drop mytable_$(i);

next

hope it helps

[jonathandienst]

Hi

You cannot perform a join within a loop, because the fields that you want added have been added to the table on the first iteration and you will be attempting to join on all fields (including the new fields) and adding no new fields on the remaining iterations.

Build the two tables (without joining them) into two resident tables, and then after the loop is complete, join the two completed tables. Remember to drop any temporary tables that are not required once the load is complete.

HTH

Jonathan

[datanibbler]

Hi Jonathan,

[I am, for the time being, the supervisor for weatherman, so I am the one who gave him this task. At this point, I'm going to take over again because this is going to be a bit complex]

Since you basically tell us that we cannot do this the way we thought about it, here is

a bit more about what we actually have to achieve, maybe you can help:

- What we are actually doing is personell_fluctuation
  => That is defined as the nr_of_emps_who_left_in_Feb (just an example)
        relative to the nr_of_emps_we_had_in_Jan

- So what I have to do for all the months we have "completed" so far (which is why I wanted to do this in a LOOP) is:

  => Extract in a RESIDENT LOAD the nr_of_emps with the "leave flag" in one specific month (WHERE clause)

  => Join to that the total nr_of_emps in the month before (another WHERE clause)

=> The two RESIDENT LOADs are from the same table, but with two different (and mut. exclusive) WHERE clauses, which is why I'd need two LOADs.

=> So, since I cannot do it in just one LOOP, I'll need two, which will give me two tables with the same nr. of records
<=> The trick is, how to JOIN those? The month would necessarily be different - I have to join the Feb_record from table A to the Jan_record from table_B.

Oh - forget it, I know. I'll create an artificial field which will be "one month behind" and make QlikView join on that.

I'll open a new thread if I run into problems, but that seems straightforward enough.

Many thanks!

Best regards,

DataNibbler