Hi All,
I need your help in understanding what's happening during my load as I'm going crazy with this very unexpected behavior.
the whole load begins with a loop:
Origine:
LOAD ..., MyField FROM my_original file;
for i=1 to fieldvaluecount('MyField')
TRACE la valeur de i est $(i);
let a = FieldValue('MyField',$(i));
inside that loop, I'm loading and dropping successively tables (doing some changes at each step):
1: load xxx Resident Origine where MyField = '$(a)';
noconcatenate
2: load xxx resident 1; drop table 1;
I reach this way table 4.
At this point, if I'm correct, the system handles 2 tables: Origine, which contains all my original data, and a temporary 4 which contains a subset of it filtered on MyField = '$(a)'
Now happens the very unexpected behavior.
From this table 4, I'm extracting 2 tables. the 1st works fine, the 2nd not:
|
noconcatenate
temp_table1: load Field1, Field2 Resident 4;
|
noconcatenate
temp_table2: load firstvalue(MyField) as MyField Resident 4
|
| Output: correct Field1 & Field2 associated to MyField when MyField='$(a)'= FieldValue('MyField',$(i)) |
Output: wrong MyField value. the loaded value is MyField = '$(a)' = FieldValue('MyField',$(1)) whatever the actual value of i |
Continuing with the script, after doing some works with my 2 temporary tables, I load again the whole table 4, adding inputs from the above work:
noconcatenate
5: load * resident 4;
left join load Field1, Field2, NewField resident temp_table1;
drop table temp_table1;
left join load MyField, NewField2 resident temp_table2;
drop table temp_table2;
drop table 4;
in this load, the actual value of MyField coming from table 4 is indeed correct as expected (i.e. if i = 2, I have the right 2nd value).
Therefore I don't understand what is wrong here:
noconcatenate
temp_table2: load firstvalue(MyField) as MyField Resident 4
I tried without "FirstValue" thinking maybe something residuing from the 1st iteration was still here, but no: when I don't put the FirstValue, I do have all the lines of the table, still with the wrong value.
which is even weirdest, is that I tested to store table 4 (store table 4 into 4.'$(i)'.qvd) and then load my temp_table2 from this stored table, and I've got the exact same result (value always as if i = 1) even if in my stored table I don't have that value... the script is rightfully loading the data from 4.'$(i)'.qvd:
Origine << Sheet1 21 717 Lines fetched
FINAL << AUTOGENERATE(0) 0 Lines fetched
la valeur de i est 1
1 << Origine 6 584 Lines fetched
2 << 1 6 584 Lines fetched
3 << 2 6 584 Lines fetched
4 << 3 6 584 Lines fetched
temp_table1 << 4 502 Lines fetched
temp_table2 << 4.1 1 Lines fetched
...
5 << 4 6 584 Lines fetched
FINAL << 5 6 584 Lines fetched
la valeur de i est 2
1 << Origine 4 931 Lines fetched
2 << 1 4 931 Lines fetched
3 << 2 4 931 Lines fetched
4 << 3 4 931 Lines fetched
temp_table1 << 4 562 Lines fetched
temp_table2-5 << 4.2 1 Lines fetched
...
5 << 4 4 931 Lines fetched
FINAL << 5 11 808 Lines fetched
BUT as you might have observed, I have an indented number that appeared when i = 2 on my temp_table2 that I didn't create: temp_table2-5 and when I look at the 3rd iteration, that number changes and becomes a 6 "temp_table2-6". I don't know if this is linked, but I don't understand either why that appears.
Any ideas here on the issue are very welcome as, as you can see, only half of the work is rightfully working from table 4.
Thanks a lot everyone for your help!
