9 Replies Latest reply on Jul 23, 2013 1:40 PM by kettan

    Counting distinct strings within an array for each row

    steve.s

      How count only once a found string within an array for every row?

       

      data table:

                event_array

      row1    257647192 257647192 257647193 257647192 257647192 257647190 263457366

      row 2   257647193 257647193 257647192

      row 3   etc.


      Answer must be 2.


      countd(find([event_array],"257647192"))????


      Any help would be appreciated!



        • 1. Re: Counting distinct strings within an array for each row
          Dimitri.B

          SUM(IIF(CONTAINS([event_array],'257647192'),1,0)

          1 of 1 people found this helpful
          • 2. Re: Counting distinct strings within an array for each row
            steve.s

            Thanks Dimitri,

            Further to the problem is renaming 257647192 into a string "eventBlack" and also have nested IF statements that counts each event number string "257647192 257647190 263457366" and renaming each one. How can I do that?

            • 3. Re: Counting distinct strings within an array for each row
              Dimitri.B

              That depends on how you want to display the renamed values. Can you post some sample data in a packaged workbook and a picture mock-up of you desired view?  (use Excel to mock-up or just draw on on a piece of paper and scan or take a photo).

              • 4. Re: Counting distinct strings within an array for each row
                steve.s

                An Example Workbook attached in first question.

                The goal is to count the fired_events and rename the fired_events strings into human readable strings. Below are some quick tables and desired results.

                 

                fired_events string name conversion in human readable form:

                1001=red

                1002=green

                1003-blue

                1004=cyan

                1005=magenta

                1006=yellow

                 

                Sum Total of all fired_events

                (this is what that graph should report)

                1001=9

                1002=6

                1003=7

                1004=5

                1005=6

                1006=2

                 

                Sum Total of all fired_events that are only counted once in row (unique id/user)

                (this is what that graph should report)

                1001=6

                1002=6

                1003=7

                1004=5

                1005=5

                1006=2

                • 5. Re: Counting distinct strings within an array for each row
                  kettan

                  I wonder if the attached workbook is what you want?

                   

                  Following 2-step technique is used:

                   

                  Step 1: CROSS JOIN

                  SELECT

                    u.[user_id],

                    u.[date_last_seen],

                    u.[date_last_event],

                    u.[fired_events],

                    e.[id (fired_event)],

                    e.[name]

                  FROM ['User Properties$'] u, ['Event Properties$'] e

                  WHERE u.[fired_events] LIKE '%' + e.[id (fired_event)] + '%'


                  Step 2: CALCULATED FIELD (Occurrences)

                  (len([fired_events]) - len(replace([fired_events], [id (fired_event)], ''))) / len([id (fired_event)])


                  Please notice:

                  Excel Jet SQL doesn't support Replace, but luckily it is supported in Tableau extracts!

                  I formatted id (fired_event) as text in Excel. (This is why I re-attached workbook as v2)

                  1 of 1 people found this helpful
                  • 6. Re: Counting distinct strings within an array for each row
                    steve.s

                    Hi Johan,

                    (Occurrences) does not see the first fired_event. See user_id=5, and it is not counting the fired_event 1002=green, even though it is in the data. Is this because it is the first event and LEN does not work?

                     

                    This does find the distincts per row but I also need one that finds the sum total of each event.

                     

                    Also do you need a Custom SQL for this to work? due to joining the two database together?

                     

                    Sorry for all the questions, I'm quite new to Tableau.

                    • 7. Re: Counting distinct strings within an array for each row
                      kettan

                      (Occurrences) does not see the first fired_event. See user_id=5, and it is not counting the fired_event 1002=green, even though it is in the data. Is this because it is the first event and LEN does not work?

                      This does find the distincts per row but I also need one that finds the sum total of each event.

                      True, there was an issue in v1, because I compared text with number.

                      The re-attached workbook with suffix v2 includes all 36 occurrences.

                      Please let me know if it is okay now.

                       

                      Also do you need a Custom SQL for this to work? due to joining the two database together?

                      Custom SQL is my preference. You can probably make 6 different measures as calculated fields, one for each event, without custom SQL. But I think it much easier to work with one measure and having the 6 events as dimensions. (I go offline now, it is late here in Faroe Islands)

                       

                      Are your data in two different databases?

                      • 8. Re: Counting distinct strings within an array for each row
                        steve.s

                        Yes, the data is in two different databases.

                         

                        I am also having a new problem now where my [id (fired_event)] is being treated as a float instead of a string causing "data type mismatch in criteria expression." error. How do I change the custom SQL to convert the float over to a string?

                        • 9. Re: Counting distinct strings within an array for each row
                          kettan

                          Which database(s) do you connect to?  Excel, Textfile, SQL Server, Oracle, etc

                           

                          I might know the conversion function. If not, I can probably find the syntax on the web.

                           

                          For Excel Jet SQL, the function is CSTR(number), and not STR as I mistakenly used in my first attached workbook with the result that the first event number was missing:

                           

                          SELECT

                            u.[user_id],

                            u.[date_last_seen],

                            u.[date_last_event],

                            u.[fired_events],

                            e.[id (fired_event)],

                            e.[name]

                          FROM ['User Properties$'] u, ['Event Properties$'] e

                          WHERE u.[fired_events] LIKE '%' + CSTR(e.[id (fired_event)]) + '%'