How to align values in a column with values from another column?

[HattrickNZ]

I basically have 2 searches that I am combining using appendcols. 1 search is for each element. It looks something like:

index=core ... ne=ne1 | stats sum(kpi1) as "kpi1" by ks_countryname | sort - "kpi1" |
appendcols [search index=core ... ne=ne2 | stats sum(kpi1) as "kpi1_ne2" by ks_countryname | rename ks_countryname as  ks_countryname_ne2| sort - "kpi1_ne2"]

the output looks something like below:

ks_countryname  kpi1  kpi1_ne2  ks_countryname_ne2
ZZ_undefined    1615    2631       ZZ_undefined
Australia      1500 1635           China
United States   676 1600         Australia
China           423  410    United States
Vanuatu       295    305           Samoa
Switzerland   220    247        Switzerland
Germany       165    213          Germany
France         157   181        France
Samoa           118   62          Vanuatu

How do I get column 3 and 4 to line up with column 1 and 2 such that:
row 2 of column 1 and 2 Australia 1500 would line up with row 3 of column 3 and 4 1600 Australia

Ideally I would still like to sort max to min by kpi1, but I just want the countries aligned also.

[woodcock · Accepted solution]

Do it like this:

index=core ... ne=ne1 | stats sum(kpi1) as "kpi1" by ks_countryname | append [search index=core ... ne=ne2 | stats sum(kpi1) as "kpi1_ne2" by ks_countryname ] | stats values(*) AS * by ks_countryname 

[HattrickNZ]

don't think this wrks. they don't seem to be lining up correctly. Also what if ks_countryname and ks_countryname_ne2 have different list of names?

[woodcock]

Actually, we don't need to use append at all and this should be even better and clearer (to give you more confidence that it is doing what you need it to do):

index=core (... ne=ne1) OR (... ne=ne2) | stats sum(eval(ne=ne1,kpi1,null())) AS "kpi1" sum(eval(ne=ne2,kpi1,null())) AS "kpi1_ne2" BY ks_countryname

[HattrickNZ]

sum(eval(ne=ne1,kpi1,null())) I am getting an error with this Error in 'stats' command: The eval expression for dynamic field 'eval(ne=ne1,kpi1,null())' is invalid. Error='The operator at ',kpi1,null()' is invalid.'

[woodcock]

I forgot the ifs; try this version:

index=core (... ne=ne1) OR (... ne=ne2) | stats sum(eval(if((ne==ne1),kpi1,null())) AS "kpi1" sum(eval(if(ne==ne2),kpi1,null())) AS "kpi1_ne2" BY ks_countryname

[HattrickNZ]

tks I should have copped that, I think thats the one 🙂 Note i had to use double quotes around ne1 sum(eval(if((ne=="ne1"),kpi1,null()))

[woodcock]

Then you need double-quotes in my solution, too. Does it work now?

[HattrickNZ]

yep thats the job, tks.