Table based on two condition (with Muiltiple ID)

[Anonymous]

I have one table like:

ID,               Type,               Status

AAA1          After                    B

AAA1          Before                A

AAA2          After                    A

AAA2          Before                A

BBB1          After                    B

BBB1          Before                A

BBB3          After                    A

BBB3          Before                C

DDD5          After                    A

DDD5          Before                C

DDD4          After                    A

DDD4          Before                B

FFF2          After                    A

FFF2          Before                A

GGG5          After                    A

GGG5          Before                C

I want to create a table with a function that compares the field "Status" when Type is like "Before" and "After" and just appears ID once. Like that:

ID,          Status Before,          Status After,          Changed?

AAAA1     A                                   B                            YES

BBB1     A                                   B                            YES

BBB3     C                                   A                            YES

DDD4     B                                   A                            YES

DDD5    C                                   A                            YES

FFF2     A                                   A                            NO

GGG5     C                                   A                            YES

How could I make this?

Thak you!

[Gysbert_Wassenaar · Accepted solution]

See attached example.

[#3]

Step 1:

One new table with distinct ID.

Table:

LOAD* distinct ID

Resident YourTable;

Step 2:

Load the Status= After:

Left Join (Table)

Load

     ID

     Status as [Status After]

Where Type='After';

Step 3:

Same for Before:

Left Join (Table)

Load

     ID

     Status as [Status Before]

Where Type='Before';

Step 4:

Compare the two values and create the new 'Changed' field. Also, clean the non interesting rows (nulls?)

[Anonymous]

Hi Iosu!

Thak you for your help!

Regards,

Debora

[Anonymous]

Hi Gysbert!

Perfect and simple solution.

Thank you for your help!

Regards,

Debora