7 Replies Latest reply on Jan 16, 2018 4:35 AM by John Hobby

# Stumped on simple LOD calc

Howdy,

So, I've been searching trying to find a solution to my problem and found sorta similar posts, but none that helps to address what I'm needing.  It could be that my search params were not correct, but tired of searching and know the great community here can help.

I have data like this:

patID     encID     svcDT     Code     pftID

123          8923     1/1/18     24342     1761

123          8923     1/1/18     L8699     1805

123          8923     1/1/18     A4565     1972

goal - create a single row for patID / encID / svcDt

patID     encID     svcDT     Primary_Code     Secondary_Code     Third_Code

123          8923     1/1/18          24342                    L8699                    A4565

Current attempt

Primary Code Formula

if index() = 1 then WINDOW_MIN(min([CPT Code]), FIRST(), LAST()) END

Primary Code Formula version 2

{FIXED [patID], [encID], [svcDT] : min([Code])}

The first formula isn't working right because the index just goes from 1...N for the entire data set.  I need it to reset for each patID/encID/svcDT combo.

The second formula seems to be 'almost' there, but I need it to take into consideration the pftID which determines the ORDER in which the code should be (primary/second/third)

I have a feeling I'm almost there, but starting to lose steam in finding help.

I know someone out there has had to do this before

Thanks!

John

• ###### 1. Re: Stumped on simple LOD calc

This would be a whole lot easier to answer with an actual workbook and sample data set.

Do you always have 3 [Code]s for every combo of patID / encID / svcDt ?

you can make your index() reset every ( patID / encID / svcDt ).  Having the sample workbook would make it easier to show you.

• ###### 2. Re: Stumped on simple LOD calc

Sorry about that ... here's a sample.

the three primary columns should all be the same, while the codes can vary in number ... the attached has three patIDs ... i need a primary column with their first code based on the pftID order, a second column with the second code based on the pftID order... if it doesn't have one, then return a NULL or just empty string.

John

• ###### 3. Re: Stumped on simple LOD calc

What constitutes “order” for s ?

Tableau doesn’t have a concept of position within the data source.

• ###### 4. Re: Stumped on simple LOD calc

What constitutes “order” for patIDs ?

• ###### 5. Re: Stumped on simple LOD calc

The pftID is considered the actual order of the records.  That field should drive the "order" for the patID / EncID / svDT combo.

• ###### 6. Re: Stumped on simple LOD calc

I think I understand what you are looking for.

In the attached Sheet 1 is your original sheet.  (I threw PftID in there.)  I added a calc called INDEX.  And I set the table calc addressing so that it walks through the PftIDs for each PatID.  You can see 1,2,3 (or less if there are less than 3 for a PatID.)  This setting will be used for all the other calcs moving forward.  The key here is to restart every PatID.

Sheet 2.  I made a First Code, Second Code, Third Code.  Again, I used those calc settings.  You'll notice that each respective value comes out only on the row it's found on.

On Sheet 3 I made a copy of the three code calcs, to wrap the calc code in a WINDOW_MAX.  This shoves the value for the code on all rows within a PatID.

Finally on Sheet 4 I dragged a copy of INDEX onto the filter shelf.  I have it display only index=1.  You may or may not need this step.  What really matters is that you have the value for each Code calc for each PatID.

• ###### 7. Re: Stumped on simple LOD calc

Many thanks Joe.  Thanks what I'm looking - the Sheet 4 - that is   I knew the index had to be involved somewhere.  I'm guessing the 'First, Second, Third' code formulas were just for demo purposes to show those calcs, as I'm not seeing them reference anywhere else.  If that's the case, that's cool how you broke it down for demo purposes.

I'll have to play around with the index piece on the filter to see if it is needed and how the display is working.  But, technically - that is what I was looking for!

Thanks,

John