search in foreach subquery

[dmitrymi]

I have items visit log index with fields: category, item each event is a visit

In addition, I have an index with all items in the system in form category, items_count

I want to create a timechart of categories: <category> -> <visited items>/<all items> other time

What I did:

index="visited" | eval cat_item = category."/".item  | timechart dc(cat_item) by category  | foreach * [ search index="cat" category="<<FIELD? >>"  | eval <<FIELD>>= '<<FIELD>>'/items_count ]

But this does not work

timechart here creates a table with categories as columns and, each row contains the count of visited items 

Now the problem is how I get column name, and value in the subquery. In the examples, the <<FIELD>> is used for the column name and column value alike. 

Please help

[ITWhisperer]

This

index="visited" | eval cat_item = category."/".item  | timechart dc(cat_item) 

does not give you a column for each cat_item as you seem to be suggesting - it gives you a count of distinct cat_items for each time period.

[dmitrymi]

Sorry my mistake this is actually:

index="visited" | eval cat_item = category."/".item  | timechart dc(cat_item) by category

but still not working. 

Without foreach the query returns number of items visited by category over time

[PickleRick]

Are you sure your problem is with fieldnames? I'd say it's with the subsearch itself.

As per the docs, foreach runs a streaming subsearch for each field. But your subsearch starts with the search command which is a generating one.

[dmitrymi]

I'm not sure, what is the right way to do it? How to select a value for each column using the column name,  and update cell value using this 

[PickleRick]

If you really need a dynamicaly constructed search, try the map command. But I'd rather do a groupped stat (i.e. count by)