Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a script that looks something like below
sub Transform(vMinDay, vMaxDay)
FOR i = vMinDay to vMaxDay
INDEX:
LOAD
A,
B,
IF(LEN(TRIM(C)) > 0, C, '<BLANK>') AS [REPORTING C],
D
FROM...$(i)
LEFT JOIN(INDEX)
LOAD,
A,
E,
F,
J
FROM...$(i)
LEFT JOIN(INDEX)
LOAD
A,
K,
L,
M
FROM...$(i)
NEXT
END SUB
CALL Transform(1,2)
I'm getting synthetic keys for every field that I create in the first load of INDEX. I bolded the fields I get synthetic keys for. For example, I get synthetic keys for the following fields: A, B, [REPORTING C] and D.
Thoughts anyone?
after the joins your INDEX table has different fields from the initial load, thus subsequent loads will not be autoconcatenated to the INDEX table.
Try with Concatenate(INDEX) preceding the INDEX LOAD.
You might have to define it in a variable after the first load to avoid an error.
Edit: would not work
hope this helps
regards
Marco
The problem is the loop! In first run the join will work as expected by key-field "A". In the next run the other fields are present too and join will take them also as key. So your join result is different from (correct) first run.
One possible workaround is to load the partial data to be joined into temp tables inside the loop and join them outside the loop. Try something like this:
(By the way: As you can see I prefer transparency for the kind of load to ensure what happens really.)
sub Transform(vMinDay, vMaxDay)
INDEX:
NoConcatenate LOAD * INLINE [A, B, 'REPORTING C', D];
_tmp_EFJ:
NoConcatenate LOAD * INLINE [A, E, F, J];
_tmp_KLM:
NoConcatenate LOAD * INLINE [A, K, L, M];
FOR i = vMinDay to vMaxDay
Concatenate(INDEX)
LOAD
A,
B,
IF(LEN(TRIM(C)) > 0, C, '<BLANK>') AS [REPORTING C],
D
FROM...$(i)
Concatenate(_tmp_EFJ)
LOAD
A,
E,
F,
J
FROM...$(i)
Concatenate(_tmp_KLM)
LOAD
A,
K,
L,
M
FROM...$(i)
NEXT
JOIN(INDEX) LOAD
//key
A,
//new values
E,F,J
Resident _tmp_EFJ;
JOIN(INDEX) LOAD
//key
A,
//new values
K,L,M
Resident _tmp_KLM;
Drop Tables _tmp_KLM, _tmp_EFJ;
END SUB
CALL Transform(1,2)
Thanks, that makes so much more sense!