Using variables in SAQL

Question

I'm trying to basically work with SAQL using some sort of variable. Not sure why every time I do that, I receive an error that the Identifier can't be found.

So, let's suppose I want to divide the revenue by a target just to see the ratio on target of my company. So let's start by loading the same database twice to get two streams:

q = load \"database\";
r = load \"database\";

Then I get some data to each stream and I assign it to an identifier:

q = foreach q generate {{column(static_2.selection, [\"value\"]).asObject()}} as 'revenue';
r = foreach r generate {{column(static_3.selection, [\"value\"]).asObject()}} as 'target';

Then we start with the tricky part, and the stuff I haven't really understood. From my understanding I need to group the two streams in a single stream using the group command. I'm not entirely regarding the groupings, should I group them by WHAT?

result = group q by all, r by all;

Then I want to do the ratio between the value and target

result = foreach result generate q.revenue / r.target as 'calc';
result = limit result 1;

So I should then be able to pass 'calc' to the step which then will display the ratio? Actually not, it gives me an identifier error, saying that the system can't recognise the value in the q.value as an identifier.

What am I doing wrong? Thank you.

Answer 1

You should be able to achieve the required output using single SAQL.

The below SAQL code should help you, where I have selected both the columns first and in the next line, the calculated value is displayed.

q = load "database";
result = group q by 'all';
result = foreach result generate {{column(static_2.selection, [\"value\"]).asObject()}} as 'revenue', generate {{column(static_3.selection, [\"value\"]).asObject()}} as 'target';
result = foreach result generate 'revenue' as 'revenue', 'target' as 'target', 'revenue' / 'target' as 'calc';
result = limit result 2000;

You can try understanding the formula using a Compare Table, wherein you can add a formula column and check the SAQL generated.

Answer 2

q = load "database";
result = group q by 'all';
result = foreach result generate {{column(static_2.selection, [\"value\"]).asObject()}} as 'revenue',{{column(static_3.selection, [\"value\"]).asObject()}} as 'target';
result = foreach result generate 'revenue' as 'revenue', 'target' as 'target', 'revenue' / 'target' as 'calc';
result = limit result 2000;

foreach statement has only one generate statement. previously it was using two