inner join in for each loop?

[#1]

Hello!

I want to do an inner join inside a for each loop but keep getting errors. I have tried a lot of variations and all statemetns works by themselves. If I har code the join statements they work fine. This is my code:

for each r in c
inner join (CurrencyRates)
load distinct YearMonth, CurrencyRate as $(r) resident CurrencyTMP where [To Currency] = '$(r)';
next

c is a list of Currency codes. What I want is a table where each currency rate is in one column.

Is ther something wrong with my code or is it not possible to have a inner join statement inside an for..each loop?

Thanks for you help!

Cheers

Fredrik

[disqr_rm]

Try putting single quotes around variable r. Like '$(r)'

for each r in c
inner join (CurrencyRates)
load distinct YearMonth, CurrencyRate as '$(r)' resident CurrencyTMP where [To Currency] = '$(r)';
next

[#3]

Thanks but I still get error but now a new error... This is the error I get:

Blank field name not allowed
inner join (CurrencyRates)
load distinct YearMonth, CurrencyRate as '' resident CurrencyTMP where [To Currency] = ''

I have loaded tha data inline instead for now. I'll try and go back to this later.

Cheers

Fredrik

[#4]

is this a place where the evaluate function would be helpful? Maybe wrap the whole statement in evaluate? Or just the where?

(Sorry, I don't for sure, just guessing)

[Oleg_Troyansky]

First, - it would be easier to troubleshoot if you posted an example...

Meantime, I can only continue guessing after everybody else... I think the root of the problem is in the syntax of " FOR EACH ... IN " and in the nature of your "list of currencies" that you called "c". What exactly is "C"? Is it a field? A variable holding the list?

If you examine a help section describing the syntax of the "FOR" loop, you'll find that you can use a literal, or an expression, or an explicit list of values - but not a field, for example. Try digging there, and you'll likely find your problem.

cheers,

[#6]

Why does the FOR loop not permit you to use a field?

What is the simplest way to evaluate a field? \

E.g. FOR EACH Variable in VariableList