6 Replies Latest reply on Jul 12, 2018 5:57 AM by Arthur Valle

# Calculating how many times a value repeats and checking previous values

Hey guys,

i have a chart kinda like this:

id1id2
type_1
num_2
46329999A1
46329999A1
46329999B1
555352C2
626321D3
6269924D3

num_2 is the "master" value, it's unique and never changes, so I should use it to identify a record (I use this concept specially in the "2nd need")

Unfortunatetly I can't share the actual chart, 'cause it's confidential. It might be important to say that it's a huge database, with over 100 million records. I've been looking for the solution to my problems for a week now, but couldn't find the right way to these things I need to get. Here are my needs:

1st: I gotta filter the records by the num_2 column. I gotta get only the records which have a value of num_2 that is present in more than 1 record. I may have a calculated field, whatever. I just need to be able to know how much records have as value of the num_2 column a value that is repeated and also know how many times those values (of the num_2 column) repeat themselves (I do not need how many times each numbers repeat, but as a total). Keep in mind i have 100 million rows and all these values in either columns are pretty huge and vary a lot.

Filtering it like I told you I need, I should get the same chart but whitout the fourth row.

2nd: Nevertheless I need to know when a record keeps the num_2 (so that means it's actually the same thing), but had its id1 or id2 changed (like the last two rows). I gotta count how many times this happens in my database.

• ###### 1. Re: Calculating how many times a value repeats and checking previous values

Hi Arthur;

You should be able to do this with a LOD expression, but without a better understanding of your database, it's difficult for me to give you precise instructions.

Are you asking for the total number of occurrences of a value (num_2) within a single dimension?

Are you asking for the total number of occurrences of a value (num_2) withing multiple dimensions?

It would be helpful if you could show a small example with result (counts).

Thank you!

• ###### 2. Re: Calculating how many times a value repeats and checking previous values

Hi, Miachael! Thanks for the answer!

I actually was trying to use the fixed function. But I still don't trust the output I'm getting. Regarding your questions, I'm asking for the number of type_1 values attached to the same num_2 value.

Lemme get a little bit more real with these data. I have the "num_2" column which is unique and allows me to identify what relates to the same "act" irl. The point is: if somethings happen irl with an already registered "act" (an existing num_2) I'll have a new record in my tableau server database which may or may not have a different type_1 value attached to. All I want is to be able to analyze exactly the records that show me something happened. In other words, if I have only one record with a num_2 value, it actually doesn't matter to this analysis we're looking foward to do. Therefore, I need to get rid of this "normal" cases. Not only do I have to be able to filter these normal cases off, but I gotta know how many times num_2 values repeat at all. That would mean like: I get all those rows with a num_2 value which appear in other rows (basically cutting off the ones that have an unique num_2 value) and count them. But also, - the thing that makes me think LOD functions may be useful here - I need to know how many times "something usually happens". How many records are there with the same num_2 value. I understand it may be kinda tricky and I don't actually know what's the best information i can get, since, as i said, this database is huge and it won't be reasoanable to like, get a graph of how many times each num_2 repeated (i have something close to 40 million num_2 values).

Does that make it any more clear? I actually didn't get what you mean with "It would be helpful if you could show a small example with result (counts)."

I'm sorry

Unfortunatetly I can't get you any prints or something like that, they're a very delicate data about huge companies and may affect whole countries if leaked. It's a really big database with different types of information. But if there's something specifically you need and you still think it's not a problem for me to send it, just tell me exactly what you need that I can try to figure someway out.

• ###### 3. Re: Calculating how many times a value repeats and checking previous values

Oh, and don't forget the second need. I gotta compare the id's whitin the num_2 values too. I mean, compare the id1 of all the records with 1 as num_2 value, as example. I gotta get how many (if any) times within all records with the same num_2, the id1 or id2 values changed.

That may actually mean i gotta compare multiple dimensions within the num_2 value (if i got your question right)

• ###### 4. Re: Calculating how many times a value repeats and checking previous values

I just tried the following calculated field to get the number of type_1 values distinctively within a num_2 value :

{FIXED [num_2] : [type_1]}

Does this make any sense in the way of getting the number of type_1 values distinctively within a num_2 value? It does, right?

• ###### 5. Re: Calculating how many times a value repeats and checking previous values

Sounds like you've got a lot going on

I was looking at it from this perspective (and I'm making up this case since the details of your own are pretty intense):

Let's say I have a list of 50 states (Alabama thru Washington)...

And each of those states have a number of Cites...

Using an LOD expression, we can relatively easily count:

How many Cites per state include the letter "A"

Cities_With_A

{fixed [City]:max(iif(contains([City],"A"),1,0))}

You can of course filter on this value so that only States with 2 or more Cities that include the letter "A" appear.

We can also change "A" into a dimension, so we run through the whole alphabet, if you want.

I realize this is a long stretch based on what you're asking, but maybe there a kernel in there that will allow you to apply it?

All the best-- and you're in good hands: the Forum is filled with clever problem solvers!

--Michael

• ###### 6. Re: Calculating how many times a value repeats and checking previous values

Hey, Michael Hesser!

That would be the actual solution to both needs. Yesterday when i read your last answer I was wondering the Fixed function would just solve the "first need". But now I realize how it can solve both, though. We have a Kernel here and that would be the way to go, at least for now.

I had tried using fixed, as I told you before, but I wasn't getting the number I should. It looks like I got things right this time and it's working fine. If I have any other problem I know I can find help over here.

Thanks a lot.

Arthur.