Retrieving Column value based on another column's value

I have 2 data sets I joined into a Joint dataset and displayed as a table in my report. 
 
Table 1 
ID1 
Summary 
State 
Severity 
 
Table 2 
ID1 
Summary 
State 
ID2 
 
In Table 2, ID2 is considered a child of ID1, but is also a valid row in Table 1 as ID1. 
 
What I'd like to figure out is the current State of ID2 and based on that state, determine whether to display the row in the Report. 
 
If i store the value of ID2 in a variable, is there a function that will retrieve it's State value based on the id? I can then take that value and determine if the row should be displayed. 
 
I've been trying to implement this through a Custom column but I can't find a function that will get the value of the State Column. 
 
Hope this makes sense..any ideas?

Find more posts tagged with

Comments

mwilliams

Hi miwalker,

Can you show some sample data from your joint dataSet and what you'd like the computed column to display based on the "state" field?

miwalker

Thanks for the reply. I attached a text file with a few sample rows.

The first 5 columns come from Table 1 and the last column from Table 2. Note the last column (ITEMID2) is also a valid value in the first column (ITEMID1). Essentially each record could potentially be a child of another and that's what's reflected in (ITEMID2).

What I want to check is for each row that contains a value in ITEMID2, what is the value in the STATE column for that record referenced in ITEMID2. Then based on it's STATE, that will determine if the record is displayed or not.

I have a separate Computed Column called Developers Priority with IF/Elseif statements like:

if Priority = "high" and Severity = "2" then 1,
else if Priority = "medium" and Severity = "3" and <computed column> = "Open" then 2,

I want the <computed column> to contain the State of the child record, so only parent records with child records that are still Open will be assigned a "2" priority.

miwalker

Michael,

Based on the sample data I attached, is it possible to do what I'm asking?

I'm assuming I'd assign the value of ITEMID2 to a variable and then use some function to get the STATE value and that's what I'll use in the Computed column. I'm just not sure the function that would do this.

Thanks,

mwilliams

miwalker,

Sorry for the delay. What would the computed column values be for the data you provided?

If I'm understanding correctly, your ITEMID2 field for your first row is the same as the ITEMID1 field in the third row, so while in the 1st row, you want to be able to check what the status is of the 3rd row item and set the 1st row's computed column value based on that? Is that correct? Or am I misunderstanding?

miwalker

Michael,

The new Computed column would contain the value in the STATE column based on the value in the ITEMID2 column.

So if I take the value in ITEMID2 (if populated), find the equivalent value in ITEMID1 (different row), then get the STATE column value for that row, that's what I want populated.

In my sample data I would take "_1zAHBA7LDdJMcZ_uEpjbI", find where it's located in the first column. This happens to be row 3 in the sample data. I would then take the STATE value for that row, "working", and populate the new computed column in row#1 with this value.

This tells me that the child record (ITEMID2) for this row is still Open as "working" is considered an Open state.

mwilliams

miwalker,

So, out of your sample data, which rows do you want to be displayed in the report? And what columns do you want to see? If you could make a larger sample of data and what you'd like that data to actually look like in the final report, that'd be great. For example, if you don't want the computed columns to show, don't show them in the "final report" view. Thanks. I think I have an idea of how to achieve the end result, if I'm understanding correctly, but I don't know if it's possible to make that computed column in the dataSet.

miwalker

I attached what the final report would look like. The ITEMID columns will not be displayed and I already have a custom column called Developer_Priority that will also be displayed.

I have an Owner parameter that determines which rows are returned, so only those records owned by the person selected and in an open state will be returned in the report.

My Developer_Priority Custom column has expressions like:

if ((row["Work Item Data Set::WI_TYPE"] == "apar" && row[Work Item Data Set::PRIORITY"] == "HIGH")){
"1";
} else if ((row["Work Item Data Set::WI_TYPE"] == "defect" && row["Work Item Data Set::PRIORITY"] == "Medium)){
"2";
} .......

I wanted to add another condition to #2 priority that says something like && ((row["Work Item Data Set::<new custom column>"] != "closed"

so only rows with child records not closed are assigned the #2 priority.

mwilliams

miwalker,

Sorry. I probably wasn't very clear. My fault!

I'm looking for a larger sample of "raw" data. How it looks in your dataSet. AND a "final report" view of what the "raw" data will look like in the report, so I can see how you handle when to show/not show certain rows, so I can completely understand the issue.

miwalker

Michael,

I was able to finally figure it out. Rather than trying to join datasets I was able to use dynamic text fields to bring in the data I needed in the table.

Thank you for the advice.

mwilliams

Great! Glad you found a solution! Let us know whenever you have a question.