#Saql0 discutindo

I have a dataset containing Opportunity Start Date, Opportunity End Date and Volume columns.

Client has asked to show a visualisation in the Dashboard where volume is distributed between Opportunity Start Date and Opportunity End Date month by month on equally basis throughtout the whole year.

For example, for below datapoints.

S No || Opportunity Start Date || Opportunity End Date || Volume

1 || 01-01-2023 || 31-12-2023 || 1200

2 || 01-06-2023 || 31-12-2023 || 2400

There will be 12 bars in the visualisation showing [ 1200 * 5/12 ] = 500 each for Jan to May

& [ 1200 * 7/12 ] + [ 2400 * 7/12 ] = 2100 each from June to Dec month.

Is there a way this can be achieved using SAQL?

Alternatively I would have to make the column to row transformation in Recipe by splitting each Opportunity row into 12 rows each for each month and divding the volume eqully.  

#CRMAnalytics #Recipe #DataTransformation #SAQL 

Hi,

I've run into a roadblock with cogrouping. I have two separate streams in my SAQL I am trying to piece back together:

q = load "MyDataset";

r = filter q by 'SomeField' == "True";

r = foreach r generate 'FieldA' as 'FieldA', 'FieldB' as 'FieldB';

s = filter q by 'SomeOtherField' == "True";

s = foreach s generate 'FieldA' as 'FieldA', 'FieldC' as 'FieldC';

t = cogroup r by 'FieldA' left, s by 'FieldA';

t = foreach t generate r.'FieldA' as 'FieldA', r."FieldB' as 'FieldB', s.'FieldC' as 'FieldC';

I get 'FieldA' working as expected, but the inclusion of 'FieldB' and 'FieldC' causes an error (it says make sure the field 'FieldB/C' exists and is spelled correctly as if it is not there). Using an aggregation function, however, works - but I do not want to aggregate values. How do I correctly join these two streams and keep the granular level of data in the other fields?

Hi All, 

Just trying to do a basic case statement in SAQL but with close dates. My goal is to do:

"Case when Closedate is between 2020. 04. 01 and 2020, 06, 30 then "Q1"....." etc...

I can't quite seem to get the syntax down, will anyone be able to help with this? Cheers!

Hi all,

I'm reviewing a dataflow with many conscutive filters node.

The first filter is (dim:N:ABC)

the second one is (dim:N:DEF)

the third is (dim:N:GHI)

ecc

There are 7 consecutive filters nodes.

Is possible to merge all these nodes?

is possible to insert in a filter node a filter logic (example: Filter A & filter B &filter B)?

tks

Hi,

I am trying to accomplish a left outer join in my dataflow and not getting the expected result. I have created two sample datasets like this:

Dataset Q: {A, B, C, D}

Dataset P: {(A,r), (A,s), (B,r), (B,s), (C,r), (C,s), (D,r), (D,s)}

Doing a left outer join on this in SQL returns the following:

Dataset QP: {(A,r), (A,s), (B,r), (B,s), (C,r), (C,s), (D,r), (D,s)}

Trying to do the same in my dataflow using an augment transformation (Dataset Q on the left, Dataset P on the right) with the lookup multiple values option returns this:

Dataset QP: {(A,(r,s)), (B,(r,s)), (C,(r,s)), (D,(r,s))}

Instead of returning multiple rows for multiple matches, it instead creates a multi-value field. Is it possible to get the results of a true left outer join in a dataflow or within an Einstein query so instead of creating a multi-value field, I instead get multiple rows returned per match?

Hi all,

I am facing issues with XMD in my sandbox on winter '21 preview:

• When I try to make changes in SAQL and try to save the dashboard, I get the error "Found multiple measures with the same 'field' value : lens_2.<field name>
• I tried to download a dataset xmd, make some changes and upload it back. However, I get an error asking me to check if it is in XMD 2.0 format. Corresponding article gives 404 error.

Can anyone help me with this issue? Thanks a ton!

Hi All, 

A basic one i assume for you guys here - trying to bind a stacked bar chart. I know how to bind two standard charts but can't seem to manage 2 stacked bar charts. What's the right way to go about this?

Cheers!

Kind Regards,

I have a Snapshot dataflow running daily. I want to take this resulting dataset into another Dataflow and then add a filter to dynamically only look at the rows of data with a Snapshot date reflecting the 1st of the month. Any hints on the SAQL I could use for this? Thanks.

FYI @Gabriel Kacarab