How to use self join

[shugup2923]

Hi All,
I have table in which I have columns such as name, id, type, business group etc
type field has 2 values 'user' or 'approver', there are some name which are both are user as well as approver for same id.

My requirement is to create a two new columns 'isapprover' and 'isuser' which will contain value yes or no.

I was able to create these two columns by using
if type is user put yes in isuser and
if type is approver put yes in isapprover.

Issue: If any name type is both user and approver I need yes and yes value in 'isapprover' and 'isuser' in every row where that name is present for same id.

Any help will be appreciated.

[richgalloway]

Try this.

index=foo (type="user" OR type="approver") | fields name, id, type. group
| eval isapprover=if(type="approver", "yes", "no"), isuser=if(type="user", "yes", "no")
| stats values(*) as * by name
| table name id group type isuser isapprover

[shugup2923]

Thanks for your efforts really appreciate your time but however it did not met my requirement

[wmyersas · Accepted solution]

If I understand you correctly, you're going to replace/reformat the current lookup to remove the type field, and instead replace it with a combination of isuser and isapprover fields. Is that correct?

(FWIW - since every one of these entries must be a user, the isuser field is going to be redundant)

Should be able to do something like the following:

| inputlookup mylookup.csv
| stats values(type) as type by name id business_group [...]
| eval isuser="Yes"
| eval isapprover=if(mvfind(type,"approver")>=0,"Yes","No")

Append a

| outputlookup mylookup.csv

line to end of the search when you verify it's exactly how you want to see it

[shugup2923]

Thanks it worked , just one change what I did is I have used eventstats as there were other fields also 🙂

[richgalloway]

If a name is both a user and approver, is that two separate rows or two values within the type field or two type fields?

[shugup2923]

It will be two different rows , for ex-
name id group type
xyz 123 abc user
xyz 123 def approver
xyz 123 ghi user

expected result
name id group type isuser isapprover
xyz 123 abc user yes yes
xyz 123 def approver yes yes
xyz 123 ghi user yes yes