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
load * from t1;
load * from t2
then in another loop:
load (list all the fields of the join) where 1=0; // this return an empty table with desidered structure
for i = 1 to 10:
load * resident mytable_$(i);
hope it helps
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.
[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.