count rows from two tables using parameters with different names

[Anonymous]

Suppose I have two following tables:

data1:

Load * inline

[

d1_ID, param1

A1, a

A2, b

A3, b

A4, c

A5, c

A6, e

A6, f

A7, f

];

data2:

Load * inline

[

d2_ID, d1_ID, param2

1, A1, a

2, A2, b

3, A2, c

4, A5, d

];

These tables are joined by d2_ID and both contain parameters (param1 and param2) with common values, but different name (in general I don't want to make a join by it). What I need is to count rows from data1 and data2 grouped by param1/param2, so in this case for example:

param          d1_count          d2_count

a                   1                      1

b                   2                      1

c                   2                      1

d                   0                      1

e                   1                      0

f                    2                      0

Any tips? I thought of making two temp tables with ID and param only (renaming param1 to param and param2 to param), then joining them using left join and treating this as a separate table... would that work?

Kasia

[sunny_talwar · Accepted solution]

Try this

Script

data1:

LOAD * INLINE [

    d1_ID, param1

    A1, a

    A2, b

    A3, b

    A4, c

    A5, c

    A6, e

    A6, f

    A7, f

];

data2:

LOAD * INLINE [

    d2_ID, d1_ID, param2

    1, A1, a

    2, A2, b

    3, A2, c

    4, A5, d

];

Param:

LOAD DISTINCT param1 as param

Resident data1;

Concatenate (Param)

LOAD DISTINCT param2 as param

Resident data2;

And then this

Dimension

param

Expressions

=Count(DISTINCT If(param = param1, d1_ID))

=Count(DISTINCT If(param = param2, d2_ID))

[Anonymous]

Works like a charm! Thank you so much!