I am working on SAQL to find Percentage of Leads that has been converted into opportunities.

I am working on wave-utils to check my SAQL and I am receiving the following error:

Cogroup projection idtoken requires stream identifier: 'converted'

The following is my code

q = load "ABCLeads";
a = group q by all;
a = foreach a generate count() as 'total';
q = group q by 'Converted';
b = filter q by 'Converted' == "true";
b = foreach b generate count() as 'converted';
d = group a by 'total', b by 'converted';
c = foreach d  generate round ((('converted')*100)/'total') as 'total_percent'; 

Is there any other better way to perform the above?


One of the ways to do it is to

d = cogroup a by all, b by all;

c = foreach d generate sum(b['converted'])/sum(a['total']) as 'sum_total_percent';

and edit you xmd --> Formats --> Measure and use a multiplier of 100.


You receive the error, because you don't supply EA with information, where it should take 'converted' from. Do that by writing b.converted.

But still, there is a logical error in your query. Lets say you have 100 in total an 50 converted. Now you cogroup them together by total and converted. As the two values are different, they won't be cogrouped in one row, but in two.

Furthermore you can skip the parts, where you group and count the individual queries. Just do that in the final step to make your code shorter.

q = load "ABCLeads";
b = filter q by 'Converted' == "true";
d = group q by all, b by all;
d = foreach d generate round (count(b)*100)/count(q)) as 'total_percent'; 

As user34789 mentioned, you can alter the apperance of the number in xmd. But you could also do that in the visual editor of the query by selecting the triangle next to that row-> Format Numbers -> Percentage

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.