concat strings at the dashboard layer?

In a table or pivot table widget I want to concatenate strings contained in two separate data model columns. Example: col1 contains "Abraham" and col2 contains "Lincoln". I want to concatenate them, along with a space to display "Abraham Lincoln" in another column

I know there is not a widget level function to do this. Would this be possible in a widget script? And if so - any idea how?

bminehart

Posted 1 year ago·Last reply 1 year ago

6 comments

bminehart

OP1 year ago

harikm007 - success w/ a Table widget, but not Pivot Table. Would it be possible in the latter?

Assaf Hanina

·1 year ago

Hey bminehart ,

In addition to the solution provided for the Table widget, you might consider performing the concatenation directly at the data source level. This can be achieved by merging columns into a single table (e.g., using a Join or Lookup) and creating a new column for the full name.

This approach allows for easy drag-and-drop functionality across various widget types, minimizing the need for additional scripts.

Best regards

bminehart

OP1 year ago

We already do this for some combinations of columns, but because we've embedded Sisense in an app that is intended to be adaptable/universal for a variety of customers and use cases, there are too many variations of string concatentations for us to build into the data layer. We really need it at the user/Dashboard layer.

DRay

·1 year ago

Hi bminehart,

If this isn't something that Sisense can do out-of-the-box, can you submit it in the Product Feedback Forum?

Thank you.

bminehart

OP1 year ago

Thanks harikm007 -- we'll give this a go and take a look at the blog posting.

harikm007

·1 year ago

Hi bminehart ,

To concatenate columns in a table widget, try these steps:

Add col1 and col2 to the table widget.
Add the following widget script. Make sure to update the variables column1_index and column2_index with the respective indices of the two columns you want to concatenate. The script will display the concatenated string in the column with index column1_index and hide the column with index column2_index.

const column1_index = 1;
const column2_index = 2;

widget.on('processresult', function(w, args){
	args.result.$$rows.forEach(item => {
		item[column1_index].text = `${item[column1_index].text} ${item[column2_index].text}`;
	})
})
//Refer:  https://www.binextlevel.com/post/hide-a-column-from-table-widget 
widget.on('domready', function(se, ev){
	
	
	$(`table tr > *:nth-child(${column2_index + 1})`, element).css('display', 'none')
	
	const elementToObserve = $('table tbody', element)[0];
	
	const observer = new MutationObserver(function(e) {

		for(const m of e) {
			if (m.type === 'childList') {
				$.each(m.addedNodes, function(index, value){
					$(value).find(`td:nth-child(${column2_index + 1})`).css('display', 'none')

				})
			}
		}
	})
										  
	observer.observe(elementToObserve, {subtree: true, childList: true});
	
})

3. Refresh the widget.

For more details on how to hide a column in the table widget, refer to this post: https://www.binextlevel.com/post/hide-a-column-from-table-widget

-Hari