Hi
I wish to dedup and consolidate customer details across two cities.
E.g. I have 2 records of the same customer across two cities and I want to consolidate them into 1 row
NewCustomerID | City1_CustomerID | City2_CustomerID | City | isActiveCustomer |
12345 | 00001 | A | Y | |
12345 | 00002 | B | N |
Result:
* Merge City1_CustomerID and City2_CustomerID into one row
* City Field populates the City where the customer is active
* Field for ExistsInOtherCities is "Y" when there are 2 or more records.
NewCustomerID | City1_CustomerID | City2_CustomerID | City | isActiveCustomer | ExistsInOtherCities |
12345 | 00001 | 00002 | A | Y | Y |
I am used to SQL where I can make temp tables in each in then join it back but unsure how to do it in SPLUNK
| eval activeCity=if(isActiveCustomer=="Y",City,null)
| stats list(City_CustomerID) as City_CustomerID first(activeCity) as activeCity dc(City) as City_count by NewCustomerID
| eval City1_CustomerID=mvindex(City_CustomerID,0)
| eval City2_CustomerID=mvindex(City_CustomerID,1)
| eval City=activeCity
| eval isActiveCustomer=if(isnotnull(City),"Y","N")
| eval ExistsInOtherCities=if(City_count>1,"Y","N")
What do your events look like? For example, do events from different cities use different field names, and what does it look like if there are more than 2 cities? Can only one city be active for the customer? Is the new customer id a different field to the city customer id?
- They use the same field names (The Data Admin provided me a lookup to use rather than streaming events)
- It cannot be more than 2 cities
- Both cities can be active but choose the first record
- The new Customer ID can sometimes be the same as the city customer id
| eval activeCity=if(isActiveCustomer=="Y",City,null)
| stats list(City_CustomerID) as City_CustomerID first(activeCity) as activeCity dc(City) as City_count by NewCustomerID
| eval City1_CustomerID=mvindex(City_CustomerID,0)
| eval City2_CustomerID=mvindex(City_CustomerID,1)
| eval City=activeCity
| eval isActiveCustomer=if(isnotnull(City),"Y","N")
| eval ExistsInOtherCities=if(City_count>1,"Y","N")