topic inner join in for each loop? in QlikView

inner join in for each loop?

Thu, 29 Oct 2009 22:17:20 GMT

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

inner join in for each loop?

disqr_rm — Sun, 01 Nov 2009 07:28:55 GMT

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

inner join in for each loop?

Mon, 02 Nov 2009 21:55:45 GMT

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

inner join in for each loop?

Tue, 03 Nov 2009 01:27:25 GMT

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)

inner join in for each loop?

Oleg_Troyansky — Tue, 03 Nov 2009 01:37:21 GMT

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,

inner join in for each loop?

Thu, 19 Aug 2010 08:16:24 GMT

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