Grouping in scripted calculation view

[former_member182337]

Hello,

I have a small doubt in the scripted calculation view.Let's say if i have to pick up fields from two tables. For simiplification , let's assume few fields alone.

TEMP1 = Select field1,field2 sum(field3) from table a group by field1;

TEMP2 = select field1,field4 sum(field5) from table b group by field3;

Now, when we call the VAR_OUT i need to join TEMP1 & TEMP2 to get the fields in a single join but VAR_OUT by default groups the dimensions i.e if i look at the generated code in data preview it is :

select field1, field2,field4,sum(field3), sum(field5) group by  field1,field2,field4;

So, my question is it mandatory to again say SUM(Measures) group by (dimensions) in the VAR_OUT or is it best if we just map the fields and the grouping will happen  automatically.

Regards,

Prem

[former_member182114 · Accepted solution]

Hi Prem,

The SQL prepared from data preview has the sum/max/min and group by as default, but you can code without it, so to avoid such behavior you should aggregate inside.

Specially in a case which you have two sources and aggregation each node alone produce a different result than join first and aggregate later as you have more keys on second case.

BTW: Your SQL's are wrong, I guess you just typed it to explain so I ignore the typo.

Regards, Fernando Da Rós

[former_member182337 · Accepted solution]

Hello Fernando,

Thanks for the reply. Could you please ellaborate a bit more with the example i had mentioned. If there are two sources and that is moved to temp1 and temp2,should i join them first and then pass it to VAR_OUT?

Thanks,

Prem

[former_member182114 · Accepted solution]

Hi Prem,

No, agregate first as you did and latter join.

Pro 1: You will join less data

Pro 2: The result at end differs if you have only one group by with both tables together (at least on what I understood from your sample).

Regards, Fernando Da Rós

But, take a look on your example... temp1 will fail as field2 is not in group by, temp2 will fail as field1 and field4 isn't on group by and field3 doesn't exist.

TEMP1 = Select field1,field2 sum(field3) from table a group by field1;

TEMP2 = select field1,field4 sum(field5) from table b group by field3;

[former_member182337 · Accepted solution]

Thank you