Building a table from single multidimensional input

[knielsen]

Hello,

I am dealing with .csv input files that list cost/amount for various components per month. Format is this:

month,cost: ABC, amount: ABC, cost: DEF, amount: DEF, cost: FOO, amount: FOO
2017-10,123.02,42,44.44,20,99.99,36

Is there a way to put it into a table like this:

Component | cost    | amount
ABC       | 123.02  | 42
DEF       | 44.44   | 20
FOO       | 99.99   | 36

Regards,
Kai.

[kamlesh_vaghela · Accepted solution]

Hi

Can you please try this?

| inputlookup my_csv_lookup | foreach "amount:*" [eval <<MATCHSTR>>='<<FIELD>>' ] | foreach "cost:*" [eval <<MATCHSTR>>='<<MATCHSTR>>'.",".'<<FIELD>>' ] | table month ABC DEF FOO | transpose | rename column as Component | search Component!="month" | eval amount = mvindex(split('row 1',","),0), cost = mvindex(split('row 1',","),1) | table Component cost amount

Thanks

[knielsen]

Thanks, that does exactly what I asked for with real data. Although now that leaves me to additional problems, I'll see what I can do about those.

Anyway, your solution is correct for me!

[kamlesh_vaghela]

Hi

can you please try this?

| inputlookup my_csv_lookup 
| foreach "amount:*" 
    [ eval <<MATCHSTR>>='<<FIELD>>' ] 
| foreach "cost:*" 
    [ eval <<MATCHSTR>>='<<MATCHSTR>>'.",".'<<FIELD>>' ] 
| fields - amount:* cost:* 
| eval Component = "ABC,".ABC."|DEF,".DEF."|FOO,".FOO 
| makemv Component delim="|" 
| fields month Component 
| mvexpand Component 
| eval amount = mvindex(split(Component,","),1), cost = mvindex(split(Component,","),2), Component = mvindex(split(Component,","),0) 
| table month Component cost amount

Thanks

[knielsen]

Works as well. I think I prefer your first solution though, makes dealing with different component names a tad bit easier.

There is no "Accept" Button to mark your solution as correct?

Regards,
Kai.

[kamlesh_vaghela]

Hi @knielsen

Kindly accept the answer and upvote my comment which helps you.

Happy Splunking