Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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;
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))
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.
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;
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;
Thank you!! You are a master