topic Re: Comparing results between rows based on column value in App Development

Comparing results between rows based on column value

OceanView — Fri, 14 May 2021 00:08:50 GMT

I am collecting data from many different runs of software and need to do comparisons between them.

I need to know the average difference between the Run 2 or 3 as compared to base. Expressed as an equation:

Example average (Case A = (97 - 50), Case B =(84-60), Case C = (88-65), Case D = (67 - 40) )= avg (47, 24,27)

Case	Run	Value
A	base	50
A	2	98
A	3	97
B	base	60
B	2	78
B	3	83
C	base	65
C	2	83
C	3	88
D	base	40
D	2	56
D	3	67

I am currently reading the data a second time and creating a second table linked by Case with the Value given a different name and when statement that only loads Run=base. This seems very inefficient as I might want to compare Run 2 to Run 3.

Is there a better way to structure the data? Or, a better way to create a chart or pivot table with set analysis? Should I break it into 3 loads where I use the where statement to separate each Run into their own table?

Side note, since I keep getting asked this: I don't want (sum(run(3)) - sum(run(base))/sum(run((base)))

Re: Comparing results between rows based on column value

Saravanan_Desingh — Fri, 14 May 2021 02:10:27 GMT

Try this,

tab1: LOAD * INLINE [ Case, Run, Value A, base, 50 A, 2, 98 A, 3, 97 B, base, 60 B, 2, 78 B, 3, 83 C , base, 65 C , 2, 83 C , 3, 88 D , base, 40 D , 2, 56 D , 3, 67 ]; Left Join(tab1) LOAD Case, Evaluate(FirstSortedValue(Value,Run=3)&'-'&FirstSortedValue(Value,Run='base')) As Key Resident tab1 Group By Case;

Re: Comparing results between rows based on column value

Saravanan_Desingh — Fri, 14 May 2021 02:10:56 GMT

Output:

Re: Comparing results between rows based on column value

OceanView — Fri, 14 May 2021 17:08:26 GMT

Thank you for the fast response, and your suggestion does work for this specific case. I was hoping there could be something done in the charts/pivot tables.

I regularly have multiple Values, which makes your solution more challenging than the one I am currently using:

load Case,

...

From [file] ();

Load Case,

Value 1 as Value1_base,

Value2 as Value2_base,

Value3 as Value3_base

From [file] (...) where Run = 'base';

This allows me to attach all of the base Values in one extra load.