PIVOT on specific element where Column made up of CSV

[tradez]

Hello Forum!

I have an attribute in my Data Model called 'Label' where each value is a CSV like "Epic, Internal, Magic"

I would like to be able to do a pivot table somehow where I am sorting by each individual one (e.g. number of flows where label="Epic", number of flow where label="Magic", number of flowers where label=Internal). I have no idea what order these labels will come in,and I am fully where that in my normal SPL i can split that value and search for these. The specific issue I have is using this attribute of my DM in a pivot setting.

Any ideas?

[woodcock]

You can do this:

... | makemv delim="," Label

Now you can do stuff like | where Label="Epic" OR Label="Magic" and also | stats count by Label.

[tradez]

Right but the issue isn't in like a tstats or SPL query, we are wanting to somehow break apart this column to use them as splittable for rows so that each one could get their own row in a Pivot table.

[woodcock]

You are going to have to change your pivot to split this out first.

[rjthibod]

@woodcock, @tradez always said in an earlier comment to the question that the Label field comes from a lookup attribute in the datamodel, so assume it can't be changed.

@tradez, @woodcock is probably correct with his makemv command. You will likely have to manually write the Pivot query given the attribute limitation.

The query would be something like this where you need to put in your datamodel and datamodel object names. You may need to change the count operation to something else if there is some other way that you determine the number of flows you are looking for.

| pivot <datamodel> <datamodel object> count(<datamodel object>) as count SPLITROW Label | makemv delim="," Label | mvexpand Label | stats sum(count) as count by Label | sort -count

[tradez]

Yes that would work, and not the issue. I should have specified, this is for the Drag and Drop UI to function. | pivot falls under the "we got it working just fine in SPL".

I think we are going to just find a different way to handle this. Thanks you guys!

[rjthibod]

Yes, you would have to fundamentally change the way you populate the data in the datamodel in order to get drag-n-drop pivot functionality for what you want.

[woodcock]

Right, you can come out of pivot and still use the pivot as a base search.

[tradez]

How? It's a value in an attribute of the data model.

[woodcock]

That's what I am saying; redo the DM.

[tradez]

But when you build a DM, and add a lookup as an attribute, you can't transform that at all. Are you saying to build a another attribute that transforms the lookup attribute? If so, i can't figure out a syntax that would do that.

[rjthibod]

Can you update the datamodel to split the values into a multi-value field? You would create a new eval attribute that performs split(label,",").

If so, then you should be able to easily do what you want.

If not, then it requires multiple steps after the initial pivot if you are doing anything more than FILTER label contains "Epic" or FILTER label contains "Magic" in the Pivot command.

[tradez]

It's a great idea but I can't get it to execute. Since the value comes from a lookup, and not the actual log i can't figure out how to do an eval on them.

Is there a way to do something like the following in an attribute definition? -> |inputlookup labelLookup | eval test=split(label, ";")

Or since i have the lookup attribute "label" what would the syntax of an eval expression based on that look like and can I do an eval expression on an attribute that comes from a lookup (e.g. is it for sure "looked up" already before that eval is executed?)

[rjthibod]

I am pretty sure that since the attribute is from a lookup, you will not be able to do the eval.

You will either have to do the pivot using the filter method for simple cases, or you will have to do a more complicated pivot with extra steps in the query.

I could help you with the query If you can share your existing syntax.