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.
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
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
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!
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
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.
Hi @knielsen
Kindly accept the answer and upvote my comment which helps you.
Happy Splunking