For each record

1

Hi.

I am new to Easymorph and still trying to find my feet.

I have read Compare two excel sheets, but cannot get it to work for me.
I have two excel files and want to compare each field. i.e

File 1:
ID Area Amount
1 BAU 100
2 SU 150
3 SU 100
4 MA 50

File 2
ID Area Amount
1 BS 50
2 SU 100
3 AU 100
4 MA 50

Result
ID Area Amount
1 Change from BAU to BS Change from 100 to 50
2 No change Change from 150 to 100
3 Change from SU to AU No change
4 No change No change

I welcome any suggestions.
Thanks

2

Hi Rykie and welcome to the Community,

In your case, the both tables can be merged on common ID, then values can be compared using an expression, e.g.

if([Area] = [Area(2)], 'No change', 'Change from ' & [Area] & ' to ' & [Area(2)])

See the example below:

changed values.morph (3.9 KB)
Book1.xlsx (8.8 KB)

image
image.png1033×594 17.6 KB

3

Thank you so much, dgudkov.

This is exactly what I wanted.

Regards

R

4

Hi Dmitry
I have a follow up question please.

From Table 1 - if I left no changes to empty, and then want to count the changes i.e

Answer:

Area Amount
2 2

Is there a better way that derive each table, filter and count?
If I have 10 columns this will mean creating 10 derive tables - as this is text and not a straight count in getting totals

Many thanks

Rykie

5

Hi Dmitry

I solved this by creating a numeric table and then using aggregation.
Not sure if there is a better way.

Thanks
Rykie

6

No need to create 10 derived tables.

I’ve updated the example with two variants how to calculate the number of changes for each column.

One variant is using an expression that returns 1 if there is a change, or 0 if not. Then the results are summed up.

The other variant is more generic as it works for any number of columns. It uses the “Unpivot” action.

changed values 2.morph (7.0 KB)

image
image.png1341×648 27.6 KB

7

Thanks.
Yes, I ended up using the @ and 0.

R