-
1. Re: Value from one column row based on query from another column
Paul WachtlerJul 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?
-
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.
-
3. Re: Value from one column row based on query from another column
Paul WachtlerJul 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.
-
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!
-
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 ?
-
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.
-
8. Re: Value from one column row based on query from another column
Paul WachtlerJul 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."
-
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.
-
10. Re: Value from one column row based on query from another column
Paul WachtlerJul 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.
-
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.
-
12. Re: Value from one column row based on query from another column
Paul WachtlerJul 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
-