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

# Counting distinct strings within an array for each row

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.

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

Any help would be appreciated!

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

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

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

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

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

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)])

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

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

(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

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

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)]) + '%'