Showing multiple related fields in a table chart

[Markbhai]

Hi All.

To give some context I needed to analyse a field which gave me a few problems.  The Field is called [NonDisclosure} and contains a very unsexy comma separated list of options, eg:   cost_reasons, withdrawn_by_requestor, repeated, etc...

I wanted to split these fields out so that I could put them into a chart and also tidy up the names so that they were presentable e.g. Cost, Withdrawn, Repeated etc.  - This I have managed to achieve (with great help from this community) by creating a new table in this Load Script:

nondisclosure_tmp:
Load
id,
informtion_non_disclosure_options;
Select
id,
informtion_non_disclosure_options
FROM `dbname`.foi;

nondisclosure:
NoConcatenate Load
id,
SubField(informtion_non_disclosure_options, ',') As [NonDisclosureReason]
RESIDENT
nondisclosure_tmp;

DROP TABLE nondisclosure_tmp;

which gives us a table something like this:

Table Name: NonDisclosure

id,  nondisclosurereason

1  Cost

1 Withdrawn

2 -

3 -

4 Cost

5 Cost

5 Repeated

5 Withdrawn

What I would like to do now is present these to the user in a Table Chart, but as one field again, so that the record which relates to id 5 would show:

Cost, Repeated, Withdrawn 

I have looked up the concat function, but cant figure out how I can look at each id and then concatenate. - Happy to do this either as an expression on the table chart or (I suspect it would be better) to create a concatenated field as part of the load script.

Thanks (again)

Mark

[rajjul]

You can try group by ID and then concat.

Try this quick sample whether it works 

load id,concat(nondisclosurereason,',') as NewProduct resident NonDisclosure group by id;

[steeefan · Accepted solution]

If you wanted to do that in a chart, you could put CONCAT(nondisclosurereason, ', ') as your expression but you would also have to have ID as a dimension in that same table.

[Markbhai]

Thanks Again Steefan

I was getting invalid dimension the first time I added it, but then realised I had to add it to the table as a Measure and not a Dimension.  Thanks again.

Mark