2 Replies Latest reply on Aug 12, 2018 9:23 AM by Yosef Sukenik

    Working with a 2 dimensional array in a single column

    Yosef Sukenik

      Background:

      I have a system for tracking production-line quality and for defective items, it records both the defect location(s) and the defect type(s) found on the item. In my DB (DynamoDB), both the locations and the types are stored in a single column as a 2-dimensional array. A given item can have an unknown number of defect locations and in each location, there can be between 1 & 9 types marked off. So a shirt might have 22 points that are checked by the quality control person and on each point, if there is a problem, they will mark between 1 & 9 defect types.

      Example records:

      1. [[Location: A; Type: 1], [Location: B, Type: 2]]
      2. [[Location: A; Type: 1,4], [Location: B, Type: 6]]
      3. [[Location: A; Type: 3], [Location: B, Type: 2,1,6], [Location: C, Type: 3]

       

      I need to figure out a way to show a % breakdown of defect types and of defect locations. I'm having difficulty with figuring out how to break apart the column into usable groups or how to count occurences of a substring. Additionally, I'm not sure how to deal with the fact that for each item, it can have a large (and unknown) number of defect locations.

       

      Any assistance in this would be most helpful.

        • 1. Re: Working with a 2 dimensional array in a single column
          Ankit Bansal

          Yosef,

           

          I am not sure how Tableau reads the DynamoDB data. But you need to pivot your data as :

           

          ITEM1, Location: A, Type: 1

          ITEM1, Location: B, Type: 2

          ITEM2, Location: A, Type: 1

          ITEM2, Location: A, Type: 4

          ITEM2, Location: B, Type: 6

          and so on...

           

          Not sure if Tableau's pivot feature can help you on this.

           

          After pivoting it will be easy for you to do any analysis.

           

          Thanks,

          Ankit Bansal

          • 2. Re: Working with a 2 dimensional array in a single column
            Yosef Sukenik

            So I understand that if I can split my column into multiple rows like that it will solve my problems.  I'm asking how to do that.

             

            Part of what's getting me is that while for my Types dimension, I know that I will have a fixed number of possible values (no location can have less than 0 or more than 9 types), my Location dimension is of an unknown size. Depending on the item, there could be 4 locations or 40. And I don't know how to deal with this.