How can I copy a field value based on another field value ?

[ctaf]

Hi,

I have the following table:

ID, Team, Department
1, Manager, A65

After performing a lookup, I've got the following:

ID, Team, Department, field_to_copy
1, Manager, A65, Team

As you can see, the lookup tells me which field is interesting and I'd like to copy.
The final result I'd like is:

ID, Team, Department, field_to_copy
1, Manager, A65, Manager

Is there an "eval" statement that could copy the value of "Team" and place it in "field_to_copy" ?
In my example it is "Team" but it could anything (i.e. "Department", but not only).

Thank you

[somesoni2 · Accepted solution]

Pheww.. this was fun. Something like this works for me (run anywhere query with sample data. replace first line with your query)

| gentimes start=-1 | eval ID=1 | table ID | eval Team="Team1" | eval Manager="Manager2" | eval field_to_copy="Team"  
| foreach * [eval field_to_copy=if("<<FIELD>>"='field_to_copy','<<FIELD>>',field_to_copy)]

[DalJeanis]

Nice. works for me too.

Now, how can we handle a NULL result?

maybe wrap with coalesce(... ,"(NULL)") ?

| foreach * [eval field_to_copy=coalesce(if("<<FIELD>>"='field_to_copy','<<FIELD>>',field_to_copy),"(NULL)")]

[somesoni2]

NULL in field_to_copy field??

[DalJeanis]

So, the trivial answer, assuming you have a small number of fields, is to use the case statement.

| eval field_to_copy = case(field_to_copy=="Team",Team,field_to_copy=="Department",Department,...) 

---

CURLY BRACES ON THE RIGHT OF THE ASSIGNMENT DOES NOT WORK.

Deleted all but the following example of curly braces on the LEFT to avoid confusing people with erroneous guesswork.

For instance, if field_to_copy had the value "Team", this would cause the variable Team to be set to "George".

| eval {field_to_copy} = "George" 

[ctaf]

Nice! But the list of fields is not predefinied (my example was simplified so that the question would not be too hard to understand) and I always avoid to hardcode things that can change. Is there another possibility to generalize it?

The following, unfortunately, does not work...

| eval field_to_copy = if(field_to_copy==*,*,"null")

[DalJeanis]

My answer was just updated to include curly braces, which I just found.

Tested, and it does not work. Still looking for a solution for you.

see somesoni2's answer, which works perfectly.

[skalliger]

At first this sounded like an easy question. However, after reading it several times I am not so sure about it anymore.

So, let's talk about your example. First of all, I need to understand what exactly you want to archieve, that's not clear to me. Your "field_to_copy" naming kinda confuses me.

So this:

Column names: ID, Team, Department, field_to_copy
Values: 1, Manager, A65, Team

Becomes this:

Column names: ID, Team, Department,  field_to_copy
Values: 1, Manager, A65, Manager

Which means simply changing the values of field_to_copy to the values of Team?
Am I correct?

If so:

Your search with lookup first followed by an eval:

search [...] | fields ID, team, Department, field_to_copy | eval field_to_copy=$team$ | table ID, team, Department, field_to_copy as Whatever_its_real_column_name_is

Is that what you are trying to do?
When I'm wrong I'll convert to comment. But maybe you can clear my confusion. 🙂

Skalli

[ctaf]

Hi Skalli,

First, thank you for trying to help me!

You're correct but your solution works only if, at the beginning, field_to_copy=Team.
Let's say field_to_copy=Department. In that case, I would like the result to be:

Column names: ID, Team, Department,  field_to_copy
 Values: 1, Manager, A65, A65

[ctaf]

If it's not doable, can I just use the value of "field_to_copy" in a where clause?

For example, I would like:

where field_A=$field_to_copy$

And the "$" sign would translate "$field_to_copy$" into "Team" so that it filter this way:

where field_A=Team

[davebrooking]

I think this answer may help you to do that?

Dave

[ctaf]

Hi Dave, I've seen this answer, but it copies the field value to create a field name, not a field value.