The purpose is to enrichment the data automatically
I did it in the UI under automatic lookup:
my lookup id AS id OUTPUT my fields_from_the_lookups
it always working but the diff here is that I'm using KVstore
Not sure where the confusion is, but no box in Advanced options accepts an expression like "| lookup mylookup id output data". (Define a KV Store lookup in Splunk Web.) Or maybe I just didn't get what the problem was.
Maybe you can explain/illustrate/screenshot how you "convert it to automatic lookup in some index"; in addition, what is "it" that you are trying to convert? Also, "not working" conveys little information in the best of situations. You need to explain the data set (including event format in the index), what fields are extracted at index time, what fields are extracted automatically at search time, etc. In particular, how is the field id obtained when you use that SPL? For example, calculated fields cannot be used in automatic lookup. The sequence of search-time operations is full of such nuances.