Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
See attached example.
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?)
Hi Iosu!
Thak you for your help!
Regards,
Debora
Hi Gysbert!
Perfect and simple solution.
Thank you for your help!
Regards,
Debora