5 Replies Latest reply: Nov 9, 2015 12:37 PM by Roger Grossi

# Count the number of changes

I have these two tables:

table 1:

Data,State,Table

a, 1, table 1

b, 1, table 1

c, 2, table 1

];

table 2:

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!

• ###### Re: Count the number of changes

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

table1:

Data,State,Table

a, 1, table 1

b, 1, table 1

c, 2, table 1

];

table2:

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))

• ###### Re: Count the number of changes

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.

• ###### Re: Count the number of changes

Maybe like attached, using this script:

table1:

Data,State,Table

a, 1, table 1

b, 1, table 1

c, 2, table 1

];

table2:

Data,State,Table

a, 2, table 2

b, 1, table 2

c, 1, table 2

];

TMP:

Change, From, To

1 --> 2, 1,2

2 --> 1, 2,1

1 --> 3, 1,3

];

LEFT JOIN

Resident table1

WHERE Table = 'table 1';

LEFT JOIN

Resident table1

WHERE Table = 'table 2';

Results:

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

Resident TMP

GROUP BY Change;

DROP TABLE TMP;

• ###### Re: Count the number of changes

Thank you!! You are a master

• ###### Re: Count the number of changes

table1:

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;