Compare two field values from same dimension but different files and add new dimension with result

[JustinM]

Hi

I'm trying to compare the values of a dimension from two different files and create a new dimension on a chart with the result of the comparison.

For example - the extract of my load looks like this:

LOAD
ID,
Status,
filename() as [fileName]

FROM A;

LOAD
ID,
Status,
filename() as [fileName]

FROM B;

Which then gives me pivotable that looks like this:

Row=ID

Column = Filename

Measure = only(Status)

	A	B
1	Active	Active
2	Active	Cancelled
3	Cancelled	Cancelled

I'm now trying to create a dimension or measure that is roughly the result of this:

if A and B ='active' then 'active', if A = 'active' and B ='cancelled' then 'Active now Cancelled' else 'cancelled'.

Is there a way to do this?

Any suggestions would be appreciated 

[hic · Accepted solution]

tmpData:
Load ID, Status as StatusA From A;
Join
Load ID, Status as StatusB From B;

Data:
Load *,
If( StatusA ='Active' and StatusB ='Active', 'Active',
If( StatusA ='Active' and StatusB ='Cancelled', 'Active now Cancelled',
'Cancelled')) as Status
Resident tmpData;

Drop Table tmpData;

[JustinM]

Great! - Much appreciated