12 Replies Latest reply on Jul 27, 2016 8:22 AM by swaroop.gantela

Creating Calculated field that compares values from 2 data source and I can use as a filter

Ben Rasilim Jun 28, 2016 9:12 PM

Hi Tableau Community.

I need help resolving this problem that I have and can't find the solution in this forum or online.

Here is the case, I have sets of tables with 1 table contains fields: ID, Item, Value and another as the reference contain limit value for each item.

below is the example of the tables I have, except I have 36million rows for table 1. (and ps, it's a tableau data extract)

the problem is, I need to create a calculated field, classifying each transaction based on the value compares to the limit

here is the example formula I used

if [Value1] > ATTR([Reference].[Limit]) then 'class1' else 'regular' end

but it's an error and to resolve the error I need to use

if SUM([Value1]) > SUM([Reference].[Limit]) then 'class1' else 'regular' end

to modify all value into aggregate so they say.

but that doesn't allow me to use the field as a dimension, which is what I need, a dimension I can use as a filter or classification.

what I need is basically a real vlookup that add an extra column to the table using using reference table.

I know that I can achieve that if I just create the detailed table in the workbook, but I need it for each record and doing it to 36million data source is just not possible.

attached is the example case

Please provide me with a solution, I've had this problem for quite a while for a lot of my report.

I need a way to do vlookup to a data extract

Thanks you for your assistance

Ben

Example table 1 : Txn

TranID	Item 1	value1
A01	A	10
A02	A	26
A03	A	13
A04	B	20
A05	B	13
A06	B	14
A07	C	10
A08	C	22
A09	C	16
A10	D	15
A11	D	21
A12	D	17
A13	E	14
A14	E	29
A15	E	21

Example table 2: Reference

Item 1	limit
A	15
B	15
C	20
D	20
E	12

Case Problem 1.twbx 16.5 KB

1. Re: Creating Calculated field that compares values from 2 data source and I can use as a filter

swaroop.gantela Jun 29, 2016 4:52 PM (in response to Ben Rasilim)
Ben,

I think you might be able to use the third suggestion on this page:
Add a Calculated Field to the Filter Shelf | Tableau Software

If your comparison is Calculation1, then you would turn it into a Table Calculation using:
LOOKUP([Calculation1],0)

I think this new table calculation can then be used a filter and as a dimension.

210062Case Problem 1.twbx 40.1 KB

3 of 3 people found this helpful
Like Show 2 Likes(2)

Actions
2. Re: Creating Calculated field that compares values from 2 data source and I can use as a filter

Ben Rasilim Jun 29, 2016 11:19 PM (in response to swaroop.gantela)

Hi Swaroop,

Thank you for your response. I don't know why I haven't come across that page. This seems to solve my problem.
I will let you know if I come into another problem.

Again, thank your for your assistance.

Best Regards
Ben
1 of 1 people found this helpful
Like Show 0 Likes(0)

Actions
3. Re: Creating Calculated field that compares values from 2 data source and I can use as a filter

Ben Rasilim Jun 29, 2016 11:19 PM (in response to swaroop.gantela)

Hi Swaroop,

Something seems to be still missing.
This doesn't allow me to flag each record as either 'class1' or 'regular', it seems to only flag what is in the workbook
when I tried to filter when the workbook is empty it only shows the option 'regular' as shown below.
is there a way I can actually flag every record row by row in the source data (tableau extract)
an actual calculated field of vlookup to a reference table.

Please help.
Thanks and regards
Ben
Like Show 0 Likes(0)

Actions
4. Re: Creating Calculated field that compares values from 2 data source and I can use as a filter

swaroop.gantela Jun 30, 2016 8:34 AM (in response to Ben Rasilim)

Ben,

It looks like at least Tran ID needs to be on the detail shelf
in order for the filter to work, or to use it as a Dimension.
Will Tran ID be in your final visualization?
If not, what fields will be there?
Like Show 0 Likes(0)

Actions
5. Re: Creating Calculated field that compares values from 2 data source and I can use as a filter

Ben Rasilim Jun 30, 2016 11:09 PM (in response to swaroop.gantela)

Swaroop,

I really appreciate you helping me. below is roughly what I had in mind for the report. there are actually another field called store which is one hierarchy above item and there are more classifications. then I only want to filter and show the class without the regular one.

The tran Id will only shows the distinct count of Tran Ids that classified as 'class1' / 'class2'
so basically show how many transactions are in each class. is it possible to do that?

Please advise
Thanks and regards
Ben
Like Show 0 Likes(0)

Actions
6. Re: Creating Calculated field that compares values from 2 data source and I can use as a filter

swaroop.gantela Jul 1, 2016 7:48 PM (in response to Ben Rasilim)
Ben,

Please see if the attached is closer to the requirement.
There are very likely easier, more straightforward ways to do this,
buy maybe this can give ideas.

I don't think the LookUp calculation is needed after all.
Mostly what seems to be needed is Window calculations.

The classification is as you described:
IF ATTR([Value1])>ATTR([Sheet1 (210062ref)].[Limit]) THEN "class1"
ELSE "regular"
END

Separate Counts and ValueTotals are made for Class1 and for the Total (including Regular).

For Count of Trans in Class1 :
WINDOW_COUNT(COUNT([Tran ID]))

For Value of Class1:
IF [classfication]="class1" THEN SUM([Value1]) END

For the Total including Regular, I used Level of Detail Calculations:
Count:
{ FIXED [Store]:COUNT([Tran ID])}

Value:
{ FIXED [Store]:SUM([Value1])}

Now, I put the [Classification] on the Columns shelf.
Of note, I right-clicked on its pill and selected to
"Ignore in Table Calculations"

Then made a calculated field to tell which Count to show:
IF FIRST()=0
THEN IF [classfication]="class1" THEN [CountClass]
    ELSEIF [classfication]="regular" THEN SUM([CountTotal])
    END
END

(Because Tran ID is on the detail shelf, this creates many copies of the count/value,
so the FIRST() was needed)

Likewise for value:
IF FIRST()=0
THEN IF [classfication]="class1" THEN WINDOW_SUM([ValueClass])
    ELSEIF [classfication]="regular" THEN SUM([ValueTotal])
    END
END

210062Case Problem 3.twbx 66.1 KB

1 of 1 people found this helpful
Like Show 1 Likes(1)

Actions
7. Re: Creating Calculated field that compares values from 2 data source and I can use as a filter

Ben Rasilim Jul 4, 2016 12:34 AM (in response to swaroop.gantela)

Swaroop,

it took me a while to understand all the formula and recreate your report on the same data.
from what I understand, this basically put all tran id into the worksheet but only shows the calculation along tables.

I haven't tried it on my real data, but I'm not sure that I can do it this way, since my real data is 36 million rows with at least 10 millions unique tran id. I'm not sure that I will be able to run the report without crashing. since it's one of my first problem handling this data.

Is there a way for me to do it without having to put the tran id into the worksheet?
Thank you for your time and patience

Regards
Ben
Like Show 0 Likes(0)

Actions
8. Re: Creating Calculated field that compares values from 2 data source and I can use as a filter

swaroop.gantela Jul 5, 2016 7:32 AM (in response to Ben Rasilim)

Ben,

Maybe we should revisit the separation of the sheets, which appears to be the source of the need for a workaround.
Level of Detail calculations may have had some benefit in simplifying things, but I think are hindered by the separation problem too.

With the classification, is that something that is changing and being updated at some time interval in the separate sheet?
How complicated of a calculation would it be, or is it mostly comparing against constants?
If it's not too bad, maybe best to create a calculation that could do the classification just off of the data sheet.
Or maybe some parameters could be created that the user would set to the needed values?

If these options are not feasible, will think on it more.
I certainly invite the community for assistance.
Like Show 0 Likes(0)

Actions
9. Re: Creating Calculated field that compares values from 2 data source and I can use as a filter

Ben Rasilim Jul 7, 2016 6:56 PM (in response to swaroop.gantela)

Hi Swaroop,

Sorry it took me a while to get back to you.
The classification is not changing that much. I would say just additional item or minor change.
I did thought about creating a really long IF function with every item in the calculation. but with 30k number of items, I thought the processing will take forever every time I want to load a report. so it's not feasible

what I'm doing right now is to create a local sql server on my computer containing my data and the reference table. that way I can join the transaction data table with reference table, not just blending it.

I realize one thing that I really want on tableau is the ability to join table from multiple data extract. not just join table from 1 database. data blending is just not enough and a bit complicated to do complex blend.

Thanks for your time and assistance.
Ben
Like Show 0 Likes(0)

Actions
10. Re: Creating Calculated field that compares values from 2 data source and I can use as a filter

swaroop.gantela Jul 19, 2016 2:01 PM (in response to Ben Rasilim)

Ben,

Would this functionality in Tableau 10 Beta help?

Integrate Your Data with Cross-Database Joins in Tableau 10 | Tableau Software
Like Show 0 Likes(0)

Actions
11. Re: Creating Calculated field that compares values from 2 data source and I can use as a filter

Ben Rasilim Jul 27, 2016 1:53 AM (in response to swaroop.gantela)

Hi Swaroop,

Yes, that looks like it would actually solve my problem. The ability to joins data from different database. Or if possible joins data between tableau extract and a database.

I look forward when I can use the feature. Do you happen to know how I can try the Tableau 10 Beta?

Sorry it took a while to reply, I was occupied with some other stuff.

Thanks for the info

Best Regards
Ben
Like Show 0 Likes(0)

Actions
12. Re: Creating Calculated field that compares values from 2 data source and I can use as a filter

swaroop.gantela Jul 27, 2016 8:22 AM (in response to Ben Rasilim)

Ben,

Here is a link about Tableau 10 beta:
Tableau 10 Beta Program | Tableau Software
Like Show 0 Likes(0)

Actions

Go to original post

Tableau Community Forums

Creating Calculated field that compares values from 2 data source and I can use as a filter

1. Re: Creating Calculated field that compares values from 2 data source and I can use as a filter

2. Re: Creating Calculated field that compares values from 2 data source and I can use as a filter

3. Re: Creating Calculated field that compares values from 2 data source and I can use as a filter

4. Re: Creating Calculated field that compares values from 2 data source and I can use as a filter

5. Re: Creating Calculated field that compares values from 2 data source and I can use as a filter

6. Re: Creating Calculated field that compares values from 2 data source and I can use as a filter

7. Re: Creating Calculated field that compares values from 2 data source and I can use as a filter

8. Re: Creating Calculated field that compares values from 2 data source and I can use as a filter

9. Re: Creating Calculated field that compares values from 2 data source and I can use as a filter

10. Re: Creating Calculated field that compares values from 2 data source and I can use as a filter

11. Re: Creating Calculated field that compares values from 2 data source and I can use as a filter

12. Re: Creating Calculated field that compares values from 2 data source and I can use as a filter

Actions

More Like This

Incoming Links