Counting distinct values between commas

[kodyjameson]

Hi Team,

I have a data set that looks like the one below, except mine is much longer with many more codes. I want to visually display the count of the top 10 Red Codes and top 10 Blue Codes.

This is what I want it to look like...

I also am going to need a list of the distinct values within both the Red Codes and Blue Codes fields to use as a dimension when I create a bar graph. I've already tried creating sub fields to separate the values by comma, but I haven't figured out a way to reference them to a dimension. If I create sub fields and use for example "Red Codes 1" as a dimension, I'll have a list of the codes, but wont be able to use the other sub fields as measures.

Hopefully this makes sense. Any help in the right direction will be greatly appreciated. Please don't hesitate to ask for clarification!

Cheers!

[henrikalmen]

Maybe like this?

splitTable:
load subfield([Red Codes], ',')  as redcode where len(trim([Red Codes]))>0 resident mytable;
concatenate load subfield([Blue Codes], ',') as bluecode where len(trim([Blue Codes]))>0 resident mytable;

From that you can count redcode and bluecode. Or maybe this to have just one field with the codes, and another field with information on what kind of code it is:

splitTable:
load subfield([Red Codes], ',')  as code, 'red' as color where len(trim([Red Codes]))>0 resident mytable;
concatenate load subfield([Blue Codes], ',') as code, 'blue' as color where len(trim([Blue Codes]))>0 resident mytable;

Is this helping somehow or do I misunderstand what you want to do?

[MarcoWedel]

Splitting the Codes using the SubField() function indeed should be a possible solution.

Can you show what you tried to achieve that did not work for you? Best using another example including one of the mentioned measures you had issues with?

[kodyjameson]

Took a while but coming back around to this as I thought I had found the solution. Giving this example

I want to 

1. Create a consolidated table of Codes (Red and Blue). 

2. Display the counts of the top 10 red codes and top 10 blue codes separately. 

3.  Count the amount of times that a code exists within the codes. For example, "RG" would be a count of 3 and "YJ" would be 3.

4. Display the historical view of the codes over time. The field to the right of blue codes is a date field. 

I've tried subfields, trimming and split tables but I'm out of ideas.

[henrikalmen]

The illustration oif the resulting table, is that actually two tables or do you want one table that looks like that? And by table, do you mean a table in the data model or a table visualization? The first example where you show what you are starting off with, is that a table in the data model or a visualization?

You say you have tried with e.g. subfields and you failed, perhaps you would like to give more detailed examples of exactly what you tried with the subfield function.

[MarcoWedel]

Hi,

maybe like this?

tabCodesTemp:
CrossTable (Color, Codes)
LOAD Date#(Date,'MM/DD/YYYY') as Date,
     [Red Codes],
     [Blue Codes]
Inline [
Red Codes;  Blue Codes; Date
AB,CD,EF ;            ; 09/01/2023
         ; EF,GR,TH,TJ; 09/02/2023
RG,YJ    ;            ; 09/03/2023
RG       ;            ; 09/04/2023
         ; TH,UK      ; 09/05/2023
         ; YJ         ; 09/06/2023
         ; YJ,TH      ; 09/07/2023
UK       ;            ; 09/08/2023
CD       ;            ; 09/09/2023
RG,ER    ;            ; 09/10/2023
] (delimiter is ';');

tabCodes:
LOAD Date,
     Color,
     SubField(Codes,',') as Code
Resident tabCodesTemp
Where Len(Trim(Codes));

DROP Table tabCodesTemp;