12 Replies Latest reply on Jul 18, 2019 6:57 PM by Paul Wachtler

Value from one column row based on query from another column

BHARGAVA SRIKANTAM Jul 12, 2019 9:05 AM

Hi I am looking to print the value from one column row based on query from another column

For example Say I have data with 10 columns and 100 rows. My first query would be Min value of column 1. Say this minimum value is from Row 10 of column 1 I want to print the value in Row 10 of column 5.

Test.twbx 859.5 KB

1. Re: Value from one column row based on query from another column

Paul Wachtler Jul 10, 2019 12:21 PM (in response to BHARGAVA SRIKANTAM)

It's potentially possible with window calcs or maybe LODs but it's totally dependent on how your data and your viz are set up. Can you share your workbook?
Like Show 0 Likes(0)

Actions
2. Re: Value from one column row based on query from another column

BHARGAVA SRIKANTAM Jul 11, 2019 3:01 PM (in response to Paul Wachtler)

Hi Paul Wachtler I attached my file in twb as twbx would be heavy. Please let me know if it works. If you see sheet 6 I am looking to query first column of every year and the remaining values should print from the same row as column 1.
Like Show 0 Likes(0)

Actions
3. Re: Value from one column row based on query from another column

Paul Wachtler Jul 11, 2019 4:24 PM (in response to BHARGAVA SRIKANTAM)

Unfortunately with a twb file, the data doesn't come over, so I'm not able to see any of your vizzes.
Like Show 0 Likes(0)

Actions
4. Re: Value from one column row based on query from another column

BHARGAVA SRIKANTAM Jul 12, 2019 10:15 AM (in response to Paul Wachtler)

Hi Paul Wachtler Just uploaded twbx file after reducing the data. Thanks Paul!
Like Show 0 Likes(0)

Actions
5. Re: Value from one column row based on query from another column

BHARGAVA SRIKANTAM Jul 17, 2019 1:44 PM (in response to Paul Wachtler)

Hi Paul Wachtler any luck ?
Like Show 0 Likes(0)

Actions
6. Re: Value from one column row based on query from another column

Paul Wachtler Jul 17, 2019 1:53 PM (in response to BHARGAVA SRIKANTAM)

Looking at the viz you attached. Here's what I'm seeing:

Which value do you want in which column and row?
Like Show 0 Likes(0)

Actions
7. Re: Value from one column row based on query from another column

BHARGAVA SRIKANTAM Jul 17, 2019 2:24 PM (in response to Paul Wachtler)

Hi Paul Wachtler In the Viz first column is hour of the day, second column is Minimum value at that hour in that year. When the query is pulling that minimum value for column 2 I want to see the remaining values based of the same row of the Data.
Like Show 0 Likes(0)

Actions
8. Re: Value from one column row based on query from another column

Paul Wachtler Jul 17, 2019 2:27 PM (in response to BHARGAVA SRIKANTAM)

Apologies, I'm not quite following. I get that you have hour of the day, and the second column is a minimum value for that hour and year. What do you mean by this part though - "I want to see the remaining values based of the same row of the Data."
Like Show 0 Likes(0)

Actions
9. Re: Value from one column row based on query from another column

BHARGAVA SRIKANTAM Jul 17, 2019 2:49 PM (in response to Paul Wachtler)

Say the value for Column 2 came from row 100 of the table I want the remaining columns to be filled from same 100th row of the data. Just that I want to be able to fill what was the situation when the worst case happened as per column 2.
Like Show 0 Likes(0)

Actions
10. Re: Value from one column row based on query from another column

Paul Wachtler Jul 17, 2019 6:22 PM (in response to BHARGAVA SRIKANTAM)

So what value do you expect to be in column 3 if it's also coming from row 100?

Your data table is pivoted, so it's not set up with multiple columns per row, you're only getting one pivot field name and value per row.
Like Show 0 Likes(0)

Actions
11. Re: Value from one column row based on query from another column

BHARGAVA SRIKANTAM Jul 18, 2019 1:58 PM (in response to Paul Wachtler)

Hi Paul Wachtler Yes my table is setup as Pivot. But I am open to see if there is any other option to see the data I want .

If you go to the data table there are multiple columns with census of each category. My second column should be based on a query which is MIN(Column Values). But for the other columns it should just print the value from same row that the data came from for column 2. Say if it also coming from same row it should be ok if not it should be made to come from same row as column 2.
Like Show 0 Likes(0)

Actions
12. Re: Value from one column row based on query from another column

Paul Wachtler Jul 18, 2019 6:57 PM (in response to BHARGAVA SRIKANTAM)
Alright Bhargava, I think I finally understand. In the column titled "Cars Available in Storage for RS", you take the minimum value. For every other column, you want to take the value for that column from the same day and time that the minimum value in column 2 came from.

I think I got it working. Here's the calculated field I created:
if [Pivot Field Names] = "Cars Available in Storage for RS" then [Pivot Field Values]
elseif [Date Time] =
    (if {EXCLUDE [Pivot Field Names]:min(
    if [Pivot Field Names] = "Cars Available in Storage for RS" then [Pivot Field Values] end)}
    =
    {FIXED datepart('year',[Date Time]), datepart('hour', [Date Time]):
    min(if [Pivot Field Names] = "Cars Available in Storage for RS" then [Pivot Field Values] end)}
    then [Date Time] end)
then [Pivot Field Values] end

I put that in place of measure values on the text shelf, also as a min() aggregation.

You have a lot of zeros in your dataset so many values coming back are zero but I think that's right. Take a look.

This is in the attached workbook. Let me know what you think.

Best,
Paul

Test_updated.twbx 825.2 KB
Like Show 0 Likes(0)

Actions