Count the number of changes

[#1]

I have these two tables:

table 1:

LOAD * INLINE [

Data,State,Table 

a, 1, table 1

b, 1, table 1

c, 2, table 1   

];

table 2:

LOAD * INLINE [

Data,State,Table  

    a, 2, table 2

    b, 1, table 2

    c, 1, table 2

];

And I want, after compare table 1 to table 2, that Result Table:

Changes, Nº

State1to2, 1

State2to1,2

State1to3,0

How can I do that? Thank you!

[swuehl]

I think you want to have 'table 2' as Table field value in your second load:

table1:

LOAD * INLINE [

Data,State,Table

a, 1, table 1

b, 1, table 1

c, 2, table 1

];

table2:

LOAD * INLINE [

Data,State,Table

    a, 2, table 2

    b, 1, table 2

    c, 1, table 2

];

Then you can create a straight table with no dimension and three expressions:

1 --> 2:

=Sum( Aggr(If(Only({<Table = {'table 1'}>} State) = 1 and Only({<Table = {'table 2'}>} State) = 2,1),  Data))

2 --> 1:

=Sum( Aggr(If(Only({<Table = {'table 1'}>} State) = 2 and Only({<Table = {'table 2'}>} State) = 1,1),  Data))

1 --> 3:

=Sum( Aggr(If(Only({<Table = {'table 1'}>} State) = 1 and Only({<Table = {'table 2'}>} State) = 3,1),  Data))

[#3]

Thank you!

That it's correct but I need the dimension "Change", how can i do that?

I want to calculate percenge of each changes.

[swuehl · Accepted solution]

Maybe like attached, using this script:

table1:

LOAD * INLINE [

Data,State,Table 

a, 1, table 1

b, 1, table 1

c, 2, table 1  

];

table2:

LOAD * INLINE [

Data,State,Table 

    a, 2, table 2

    b, 1, table 2

    c, 1, table 2

];

TMP:

LOAD * INLINE [

Change, From, To

1 --> 2, 1,2

2 --> 1, 2,1

1 --> 3, 1,3

];

LEFT JOIN

LOAD Data, State as StateFrom

Resident table1

WHERE Table = 'table 1';

LEFT JOIN

LOAD Data, State as StateTo

Resident table1

WHERE Table = 'table 2';

Results:

LOAD Change,

  Count(if(From=StateFrom and To=StateTo,Data)) as Count

Resident TMP

GROUP BY Change;

DROP TABLE TMP; 

[maxgro]

table1:

LOAD * INLINE [

Data,State,Table

a, 1, table 1

b, 1, table 1

c, 2, table 1  

];

join 

LOAD Data,State as State1,Table as Table1   INLINE [

Data,State,Table 

    a, 2, table 2

    b, 1, table 2

    c, 1, table 2

];

left join (table1)

load *, State & '-->' & State1 as Change

Resident table1;

[#6]

Thank you!! You are a master