Is there an alternative to the Appendcol command?

[VI371887]

Need help!!!

I am intending to make a table with the country wise sum(percent90). If i do the below, it will just sum percent90 per FUNCTION

index=int  source="*" FUNCTION=* | stats sum(PERCENT90)  by FUNCTION

So I tried using append which...

index=int  source="HK" FUNCTION=* | stats sum(PERCENT90) as HK by FUNCTION |
append  [search index=int  source="SG" FUNCTION=* | stats sum(PERCENT90) as SG by FUNCTION]

...would give the below output

FUNCTION           |  HK        |              SG
AGE             |107.773        |
CLT                |49.206      |
COM             | 7.497     |
RIO             |56.803     |
AGE            |               |            120.644
CLT                 |         |             37.6
COM                 |          |              61.778
CONSULT     |                  |             10.115

What I am looking for is as shown below, no repetition of Function name below.

FUNCTION           |  HK        |              SG
AGE                |107.773     |         120.644
CLT                    |49.206      |         37.6
COM                | 7.497      |         61.778
RIO                |56.803      |            0
 CONSULT            |        0        |             10.115

So i tried the appendcol command, but it messes up the data, like in the above example, if Function Consult and RIO don't have a value for any country, it would show "0 " instead Appendcol with below query does..

 index=int  source="HK" FUNCTION=* | stats sum(PERCENT90) as HK by FUNCTION |
    appendcols  [search index=int  source="SG" FUNCTION=* | stats sum(PERCENT90) as SG by FUNCTION] 

FUNCTION           |  HK        |              SG
AGE                |107.773     |         120.644
CLT                    |49.206      |         37.6
COM                | 7.497      |         61.778
RIO                |56.803      |          10.115
 CONSULT            |              |         

It fills the details from Consult in RIO for SG which is wrong.

What can be done here?

[knielsen]

I'd actually avoid append here.

index=int  source="HK" OR source="SG" FUNCTION=* | chart sum(PERCENT90) over FUNCTION by source

should be faster...

[VI371887]

Thanks!! this makes it more minimal and easy to migrate.

[harishalipaka · Accepted solution]

hi @VI371887

try like this with your second approach

 index=int  source="HK" FUNCTION=* | stats sum(PERCENT90) as HK by FUNCTION |
 append  [search index=int  source="SG" FUNCTION=* | stats sum(PERCENT90) as SG by FUNCTION] |stats values(*) as *  by FUNCTION

[VI371887]

Thanks!! It worked but can you help me understand what it is doing ?

[harishalipaka]

It will adjust the values based on Function field.