Forum Discussion

Nina1's avatar
Nina1
Data Storage
06-23-2023

Dashboard Graph to - Create unique categories from a Markets column that contains multi values

I am new to Sisense and working on creating a dashboard. I have 2 fields in an excel that contains several values, but separated with a colon and #. I need to create a Graph that displays each unique value e.g "Market" - Germany is listed in several rows and so are other markets in the same cell. I need to pull out the total number of occurrences / impacts for that market. I have the same issues with he column "Region". How can i go about to create a Custom table in order to link it to my Graph widget. My Graph - the markets come out as
1. Austria;# Germany
2. Austria;# Germany;#Belgium/Lux;# Czech Republic,
But need each market to be a separate Category
Guidance would be much appreciated. I have attached a copy of an example from excel Colum's U & V

Customizations
Sisense.JS

2 Replies

Replies have been turned off for this discussion
  • irismaessen's avatar
    irismaessen
    Data Pipeline
    06-28-2023

    Splitting lists or arrays into rows is not one of the strongest points of Sisense Elasticubes -- the value from this column will get imported as a single text value. You _can_ then isolate the first, second, third... etc value using string manipulation functions but doing this exhaustively means knowing the maximum number of values you can have in this array and repeating the isolation that many times (and unioning all the results)

    Like this:

    select id, StrParts(Market, ';#', 0) as market from Sheet1 where not IsNullOrEmpty(StrParts(Market, ';#', 0)) 
union all 
select id, StrParts(Market, ';#', 1) from Sheet1 where not IsNullOrEmpty(StrParts(Market, ';#', 1)) 
union all
select id, StrParts(Market, ';#', 2) from Sheet1 where not IsNullOrEmpty(StrParts(Market, ';#', 2)) 
union all 
select id, StrParts(Market, ';#', 3) from Sheet1 where not IsNullOrEmpty(StrParts(Market, ';#', 3)) 
union all
select id, StrParts(Market, ';#', 4) from Sheet1 where not IsNullOrEmpty(StrParts(Market, ';#', 4))



    You might be better off making use of Excel tooling  (maybe even PowerQuery) to create the expansion table that has multiple rows for every rowid (or unique identifier) table row in your main table, and all the values in your 'Market' field so that there is a *single* value in the Market field. Then import both (/all three) tables into Sisense and join them on the rowid in your cube/model.

    Something like:

    1. Austria
    1. Germany
    2. Austria
    2. Germany
    2. Belgium/Lux
    2. Czech Repblic

    • Nina1's avatar
      Nina1
      Data Storage
      06-28-2023

      Thanks, I went with the Excel Power Query and solved my issue.