Symmetric difference to get changes between years

[jsjohann]

Hello, I'm looking to achieve the following:

I do have a table consisting of data entries like that:

State Institute Period Permission

State A	Institute A	Period 1	1
State A	Institute B	Period 1	1
State A	Institute C	Period 1	0
State A	Institute D	Period 1	0
State A	Institute A	Period 2	0
State A	Institute B	Period 2	1
State A	Institute C	Period 2	1
State A	Institute D	Period 2	0

(...)

My goal is to display the changes of Permission between certain periods in a map. So, if there is a change of a permission for an institute that I can color the certain state and display the corresponding institutes with a change.

I thought of using a symmetric difference operator in a  chart expression to first get a list of all institutes with a permission in Period 1 in a certain state:

State Institute Period Permission

State A	Institute A	Period 1	1
State A	Institute B	Period 1	1

then second to get a list of all institutes with a permission in Period 2:

State Institute Period Permission

State A	Institute B	Period 2	1
State A	Institute C	Period 2	1

and after that using a XOR to get only the changed institutes:

State Institute Period Permission

State A	Institute A	Period 1	1
State A	Institute C	Period 2	1

Now, I tried putting a formula together to display the changes in a map but this isn't working as aspected as this simply displays all the institutes with a permission disregarding the period or the XOR:

concat({<Permission={1}, Period={'Period 1'}> / <Permission={1}, Period={'Period 2'}>} distinct Institute, ', ')

Can anybody help me? Thank you!

[lironbaram · Accepted solution]

Hi, 
I've created in the script a field that is called Dim with this expression :
 

[Table]:
load *,
     State & '_' & Institute  as Dim;
LOAD * INLINE 
[
State,Institute,Period,Permission
State A,Institute A,Period 1,1
State A,Institute B,Period 1,1
State A,Institute C,Period 1,0
State A,Institute D,Period 1,0
State A,Institute A,Period 2,0
State A,Institute B,Period 2,1
State A,Institute C,Period 2,1
State A,Institute D,Period 2,0
](delimiter is ',');

then I used this expression to get the desired results:

max({<Dim=P({<Permission={1}, Period={'Period 1'}>}Dim) /p({<Permission={1}, Period={'Period 2'}>}Dim)>}Permission)

see attached app

[jsjohann]

Thanks a lot! This seems to be working like expected.