Using dimension values of an Island table for calculating its expression

[#1]

Hi,

I get this island table:

Platform_ID	Type	Revenue
1	Managed Advertisers	XXX
1	Linked Advertisers
1	Managed Publishers
1	Linked Publishers
2	Managed Advertisers
2	Linked Advertisers
2	Managed Publishers
2	Linked Publishers

I want to calculate an expression for this table (the Revenue), using data from another table, without connecting its dimensions to the other table.

For example, if i could do somthing like this:

For the calculation of revenue for cell XXX: give me the revenue from other table that has a field1 that is equale to 1,

and that has field2 that is equal to Managed Advertisers.

How this could be done?

Thanks,

Guy

[Anonymous]

Your table will remain an island so long as there are not fields that are the same name as another table's fields.

You can join on the data from another table as long as you don't leave a same named field.

Try something like this:

LEFT JOIN (DateIslandTable) LOAD

     Field1 AS Platform_ID,

     Field2 AS Type

     sum(Field3) AS Revenue

RESIDENT RevenueTable

GROUP BY Field1, Field2;

The group by clause is probably needed, to prevent a cartesian join and to support your summing up of the revenue figure (I've called it Field3 and assumed it comes from the same table as Fields 1 and 2, i.e. what I am calling RevenueTable).

Jonathan

[#3]

sum(if(Platform_ID = field1, Revenue))

But use with care, it can show worng results, it depends on your data model.

[#4 · Accepted solution]

Hi,

Eventtually i understood that i need to connect my "island" table with a combined key (Platform_ID + '|' + Type).

Thanks,

Guy