6 Replies Latest reply on Mar 28, 2012 4:49 PM by Richard Leeke

    Table Calculation Lookup Partitioning Help

    Alex Kerin

      I have what seems like a simple problem, but cannot figure it out. I have a shift ID from the datasource. Multiple rows may have this shift ID, all with different In Punches and Out Punches. I want to create a view with a single row for each Shift ID with all of the other punches.

       

      The first and last punch are simple - using max and min. For the others I thought a calculation like

       

      lookup(attr([In Punch DTM]),first()+1)

       

      would work with the appropriate partitioning to get the second In Punch for example.

       

      Instead I get Nulls. Workbook attached. I cannot use max(In Punch) as there may be more than 2 punch blocks for a shift ID. That is not the case for the mock data attached.

       

      Footnote: Richard Leeke and Joe Mako may recognize this. I was creating the shift ID in Tableau. Doing this created a complex issue with trying to get the correct views (as you can't use a table calculation to partition another table calc (http://community.tableau.com/ideas/1194). Richard did an excellent job of hiding extra data that wasn't needed, but in the end the complexity causes crashes/slow downs that meant creating the Shift ID elsewhere made more sense.