for each loop error

[anonymous1]

hi, 

im having an issue with my 'for each loop'.  basically i need this loop to loop through another table, copy all the records that have a PROTOCOL_ID ending in 'A', and insert them into a new table. I have loaded the exact table structure as in the other table, with the only amendment being that i need the FLAG column to now populate with 1, whereas in the other table its NULL. this script is in the Execute Layer of my QV app. could someone please advise as to why the following script is having an issue:

for each PROTOCOL_ID in OTHER_TABLE

NEW_TABLE:

load
 
PROTOCOL_ID,
COUNTRY,
Date(MONTH, 'DD-MMM-YYYY') as MONTH,
num(AMOUNT) as AMOUNT,
1 AS FLAG

resident OTHER_TABLE
where right(PROTOCOL_ID, 1) = 'A';

NEXT

[Miguel_Angel_Baeyens · Accepted solution]

Which is the issue?

If that is the actual code, note that you cannot use field names in the FOR EACH loop, you first need to get all possible values in the form 'value1','value2','value3', so from the top of my head:

Temp_AllProtocolIDs:
LOAD Chr(39) & Concat(DISTINCT PROTOCOL_ID, Chr(39) & ',' & Chr(39)) & Chr(39) AS ALL_PROTOCOLS
FROM Source;

LET vAllProtocols = FieldValue('ALL_PROTOCOLS', 1);

DROP TABLE Temp_AllProtocolIDs;

FOR EACH vProtocol in $(vAllProtocols) // this might need to be between single quotes

NEW_TABLE:
LOAD
// Fields
RESIDENT OTHER_TABLE
WHERE PROTOCOL_ID = '$(vProtocol)' // so, the current value in the loop
AND RIGHT(PROTOCOL_ID, 1) = 'A'; // and only if it ends with "A"

NEXT

I'm positive there are simpler ways to do this, but at least you get a glance of the code you can use in a FOR EACH NEXT loop.

Check quotes and dollar sign expansions, I wrote this from memory.