How to flatten xyseries results multiple columns?

[interrobang]

Trying to do a cross-reference multi-search that gathers specific result counts for two outputs (column1 & column2). Each search ends with a stats count and xyseries, combined to generate a multi-xyseries grid style spreadsheet, showing a count where theres a match for these specific columns. Count doesn't matter so all counts>=1 eval to an "x" that marks the spot.
eg.

col1	col2	1	2	3	4	5	6	7	8	9	10
a
b
c		x		x
d									x	x
e			x						x		x
f							x
g
h								x
i											x
j						x
	k
	l
	m					x
	n
	o		x	x				x			x
	p						x
	q						x
	r						x
	s						x
	t						x

The overall query hits 1 of many apps at a time -- introducing a problem where depending on the app, the rows in particular can become very large, especially against column2's search.

For the situations with an app search that generates pages and pages of results against column2, i'd just like to default to flatten/squish/merge (not sure the best terminology) the result rows, ideally for both xy grids for col1 & col2 results, so you can see the columns 1,2,3..10...x  to see what matches and what doesnt at a glance

eg. squishing col2

col1	col2	1	2	3	4	5	6	7	8	9	10
a
b
c		x		x
d									x	x
e			x						x		x
f							x
g
h								x
i											x
j						x
	squished		x	x		x	x	x			x

i think the terminology for what this might be is throwing my searches but I've tried a number of things from dedup to merge andjoin and stats, but all seem to have shortcomings. closest ive got is adding

| stats values(*) as *
| eval col2 = if(col2="*", "squished", "squished") 

to just treat col2 rows as all the same, but i've found cases where this removes empty columns altogether eg. ones that don't have an "x" count or grid match, for example column 4 would just get removed. This might be more related to structure of my overall query, but thats kinda why im wondering if theres just some spreadsheet type function to after-the-fact, flatten/squish  the results in an xyseries like this, and the dash can toggle between squished and expanded.

[bowesmana]

Does this work?

| eval col1=if(isnull(col1) OR len(trim(col1))=0, "zz", col1)
| stats values(*) as * by col1
| table col1 1 2 3 4 5 6 7 8 9 10

the eval col1 is to handle the possible cases where col1 is empty - depends on your data. 

If you want to have a col2 as 'squished', you can simple put a 

| eval col2=if(col1="zz", "squished", null())

and add that into the table

[interrobang]

Oops > No luck, just seems to blank out col1. 

[bowesmana]

There's nothing in that that should "blank out" col1 - can you post your search and a screenshot.

[interrobang]

sorry using "| table col1 *" as col & row fields are dynamic, meant it seems to have removed a column based on it having no results against col1 & 2.  this is the output eg. column 4 gets disappeared.

col1	col2	1	2	3	5	6	7	8	9	10
a
b
c		x		x
d								x	x
e			x					x		x
f						x
g
h							x
i										x
j					x
zz	squished		x	x	x	x	x			x

what i ran into before, gonna have to fillnull and then clean it up in post or something.

[bowesmana]

OK, got it, so if your columns are dynamic, how would you know there should be a column "4" in order to fillnull?

[interrobang]

the namings dynamic, so i cant force column names to be static

the search output to grid showing the relationships between x1 & y1 & x2 & y2 -- is my source of truth.

manipulating it to flatten the results is currently altering the source of truth. column4 in this example, but others may have many many more that disappear. thats why im hoping theres some excel-esque method to reduce the results in this fashion, without disappearance. 

[bowesmana · Accepted solution]

I am not sure how the column will disappear - if there is no row in your data that contains a value 'x' for field Y as Y has no value anywhere, then isn't the source of truth that column 'Y' should not be present.

I think I'm not getting the issue, as the stats values(*) as * will always collect all columns that are present.

What about this to create fields for every row with a blank value?

| foreach * [ eval <<FIELD>>=if(isnull('<<FIELD>>'), " ", '<<FIELD>>') ]

[interrobang]

nice that did it, placed before the eval Col1=if(isNull(col1).... better because my 'fillnull value' route was starting to become more troublesome... values merged in many cells...

and yeh its counter-intuitive with what should be present, cause the main query intent is to show x vs y relationships, but the non-relation is just as important -- basically its a permissions grid and so the blanks show where no access is permitted.

still not sure why its disappearing columns either, but the main query itself is quite complicated and has some search NOT checks to filter/qualify things. the query itself is a block and the outputs very specific grid tho, so yeh...this has been puzzling why attempts to manipulate the post-query output, remove things they seemingly shouldnt.