topic Re: How to find missing values from different lookups? in Reporting

How to find missing values from different lookups?

Mary666 — Sat, 21 Nov 2020 05:43:46 GMT

I have two different lookups and I need to compare and find the missing employee names. If present in one lookup it should display yes and if not present it should display no. Here is an example.

Lookup 1:

Sam

Sheila

James

Lookup 2:

Sam

Sheila

James

Tom

Then create two columns and display yes or no if name is present or not.

Employee Names	Lookup 1	Lookup 2
Sam	YES	Yes
Sheila	YES	Yes
James	YES	Yes
Tom	No	Yes

Re: How to find missing values from different lookups?

ITWhisperer — Sat, 21 Nov 2020 09:31:54 GMT

Read the first lookup, evaluate a variable unique to that lookup if there isn't one already. Append the other lookup with a unique value for that lookup. Do a stats values(lookup1unique) values(lookup2unique) by employee name. Then where either lookup1unique is null or lookup2unique is null, you have a missing employee name

Re: How to find missing values from different lookups?

Mary666 — Sun, 22 Nov 2020 07:19:46 GMT

@ITWhisperer

I'm new to Splunk and I am still struggling with the concept. I was able to join the lookups, and all their different values are listed, but I am still not able to get Yes where the Employee Name exists or No - which could be in either CSV. I would like to have 2 separate column for each CSV and under it either a yes or a no. Any advice at this point is greatly appreciated.

I think my main issue is just getting to find away to use the csv as a filed in Splunk.

| inputlookup Employee_Names1.csv | inputlookup append=t Emplyee_Names2.csv | rename "First Name" as FirstName "Title" as Role | eval Emplyee=if(Employee_Names1.csv ="EmplyeeName","Yes", "No") | eval Emplyee=if(Emplyee_Names2.csv ="EmplyeeName","Yes", "No") | stats values(Employee_Names1.csv) as List1, values(Emplyee_Names2.csv) as List2 by Emplyee name

Re: How to find missing values from different lookups?

Mary666 — Sat, 12 Dec 2020 01:20:12 GMT

I figured it out,

So here I am comparing lookups. If name is in lookup 1 display Yes otherwise No, if in lookup 2 display yes otherwise no.

I had used static values on my dropdown filter for the dashboard. Therefore, the 0,2,1--- 0 means its in both reports, 2 means its in Lookup 1, and 1 means its in lookup 2.

| New Field Name Created to identify the name=if(Lookup_1="Yes", if(Lookup_2="Yes",0,2),1)