topic result combination in Dashboards & Visualizations

result combination

lostcauz3 — Tue, 30 Nov 2021 08:53:44 GMT

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

Re: result combination

ITWhisperer — Tue, 30 Nov 2021 09:44:45 GMT

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

Re: result combination

lostcauz3 — Tue, 30 Nov 2021 10:11:54 GMT

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

Re: result combination

ITWhisperer — Tue, 30 Nov 2021 10:21:32 GMT

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.

Re: result combination

lostcauz3 — Tue, 30 Nov 2021 13:02:53 GMT

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.

Re: result combination

ITWhisperer — Tue, 30 Nov 2021 13:10:33 GMT

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.

Re: result combination

lostcauz3 — Tue, 30 Nov 2021 14:31:50 GMT

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

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

Re: result combination

ITWhisperer — Tue, 30 Nov 2021 14:41:55 GMT

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.

Re: result combination

lostcauz3 — Tue, 30 Nov 2021 16:46:09 GMT

thanks a ton,

Solved it!