4 Replies Latest reply: Aug 2, 2011 8:43 AM by Matan Chalamish

# How to load 2 rows to 1 row according parameter

Hello All.

It's look like very simple question...

Please look at the script on the attached qvw.

In this script I has:

ACT, PARAM, COEF

1, W, 3.66

1, M, 1

2, W, 2.5

2, M, 1

3, W, 3.4

3, M, 1.5

];

Now, I want to get a table without duplicity. In this table - for every ACT it will be only 1 row.

So, if the PARAM = 'W' the COEF falue will be under Final.WorkerCoef

AND, if the PARAM = 'M' the COEF falue will be under Final.MachineCoef

Actualy I want to show a Table Box (NOT Chart) look like:

 Final.Act Final.MachineCoef Final.WorkerCoef 1 1 3.66 2 1 2.5 3 1.5 3.4

Any suggestions?

Thank you!

• ###### How to load 2 rows to 1 row according parameter

Hi,
I would do it with LEFT JOIN, like this:

FinalAct:

ACT AS Final.Act
,
COEF AS Final.WorkerCoef
RESIDENT Act
Where PARAM = 'W';

Left Join(FinalAct)
ACT AS Final.Act
,
COEF AS Final.MachineCoef
RESIDENT Act
Where PARAM = 'M';

Regards, Miha

• ###### How to load 2 rows to 1 row according parameter

Thanks Miha - It's Works!

Maybe there a way to go over Act only once?

Thks again -

Matan.

• ###### How to load 2 rows to 1 row according parameter

You could also do it with preceeding load, but it's a bit harder to understand.

Final.Act
,
MAX(Final.WorkerCoef) As Final.WorkerCoef
,
MAX(Final.MachineCoef) As Final.MachineCoef
Group by Final.Act;
ACT AS Final.Act
,
If (PARAM = 'W', COEF) AS Final.WorkerCoef
,
If (PARAM = 'M', COEF) AS Final.MachineCoef
RESIDENT Act;

The second part is your's and you get 6 rows. In preceeding load (first part) I just aggregated the max values.

You have to be careful - these versions only work when you have one W and one M transaction - otherwise you should use some other aggregation function.

Regards,Miha

• ###### How to load 2 rows to 1 row according parameter

Thank you Miha.

Thats exactly what I was looking for.

Regards
Matan.