Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
JustinM
Contributor III
Contributor III

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

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 

Labels (2)
1 Solution

Accepted Solutions
hic
Former Employee
Former Employee

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;

View solution in original post

2 Replies
hic
Former Employee
Former Employee

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
Contributor III
Contributor III
Author

Great! - Much appreciated