1

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.

Improve this question
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.

Improve this answer
5
  • "q = load \"database\"; result = group q by 'all'; result = foreach q generate {{cell(whatever.result, 0, \"whatever\" ).asObject()}} as 'revenue', generate {{cell(whatever1, 0, \"count\" ).asObject()}} as 'target'; result = foreach result generate 'revenue' as 'revenue', 'target' as 'target', 'revenue' / 'target' as 'calc'; limit result 1;" I've used you code, but I get the following error: This widget can't be displayed because there is a problem with its source step: Syntax Error at position 219 after token generate :: nue', generat⇾e⇽ 27378507.8713 – Eliijahh Oct 3 '17 at 14:09
  • I might have missed something syntactically, can you verify using a compare table and adding the formula column in it? – Hemant Jain Oct 3 '17 at 14:12
  • May be double quotes for the formula of selection – Hemant Jain Oct 3 '17 at 14:13
  • Thing is that using a same 'q', means that I can't filter in a different way. The problem is when I have two different values from two different calculations/set of filters and I want to bring them together so that I can divide or sum the two. I tried doing result = group a by 'all', q by 'all'; but unfortunately that doesn't seem to then allow me to get the target and value from the two streams. – Eliijahh Oct 3 '17 at 15:17
  • See if this link helps: Here union is used to combine two datasets: salesforce.stackexchange.com/questions/184876/… – Hemant Jain Oct 4 '17 at 11:14
0
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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.