result combination

[lostcauz3]

ORG	Month	KPI_1	KPI_2	KPI_3	KPI_4
first	Sep21	100	NA	NA	NA
first	Sep21	NA	100	NA	NA
first	Sep21	NA	NA	100	NA
first	Sep21	NA	NA	NA	100

how do i convert the table above  to get  the table below:

ORG	Month	KPI_1	KPI_2	KPI_3	KPI_4
first	Sep21	100	100	100	100

[ITWhisperer · Accepted solution]

| foreach KPI_*
  [| eval <<FIELD>>=if(<<FIELD>>="NA",null(),<<FIELD>>)]
| stats values(*) as * by ORG Month

[lostcauz3]

thanks for the suggestion this is working but there is something with my data that i am missing i guess that's why extra "NA" is being added to the fields containing 100.

[ITWhisperer]

You could something like instead

| eval <<FIELD>>=if(match(<<FIELD>>,"NA"),null(),<<FIELD>>)

but at the end of the day, you have access to your data and can find out what's really going on.

[lostcauz3]

ORG	Month	KPI_1	KPI_2	KPI_3	KP_4
first	Sep21	100 NA	100 NA	100 NA	100 NA

output is coming like this for the above query you mentioned

[lostcauz3]

this is happening after stats values(*) as * by ORG,MONTH.

any way i can delete the multivalues of these fields that is NA @ITWhisperer 

[ITWhisperer]

Anything that is NA should have been set to null() by the foreach command, so anything that is left should not be NA but somehow something else. You need to examine these more closely to figure out why they are not being set to null(). For example, is there a trailing space? or is it N/A? etc.

[lostcauz3]

thanks a ton,

Solved it!

[ITWhisperer]

Here is a runanywhere example showing it working

| makeresults
| eval _raw="ORG	Month	KPI_1	KPI_2	KPI_3	KPI_4
first	Sep21	100	NA	NA	NA
first	Sep21	NA	100	NA	NA
first	Sep21	NA	NA	100	NA
first	Sep21	NA	NA	NA	100"
| multikv forceheader=1
| table ORG Month KPI*
| foreach KPI*
    [| eval <<FIELD>>=if(<<FIELD>>="NA",null(),<<FIELD>>)]
| stats values(*) as * by ORG Month

This probably means your real data does not match your example data closely enough.