topic How to load 2 rows to 1 row according parameter in QlikView

How to load 2 rows to 1 row according parameter

matancha — Tue, 02 Aug 2011 11:59:44 GMT

Hello All.

It's look like very simple question...

Please look at the script on the attached qvw.

In this script I has:

LOAD * INLINE [

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

Tue, 02 Aug 2011 12:06:48 GMT

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

FinalAct:

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

Left Join(FinalAct)
LOAD
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

matancha — Tue, 02 Aug 2011 12:25:47 GMT

Thanks Miha - It's Works!

In your answer the script run twice on the Act table.

Maybe there a way to go over Act only once?

Thks again -

Matan.

How to load 2 rows to 1 row according parameter

Tue, 02 Aug 2011 12:35:32 GMT

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

Load

Final.Act
,MAX(Final.WorkerCoef) As Final.WorkerCoef
,MAX(Final.MachineCoef) As Final.MachineCoef
Group by Final.Act;
LOAD
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

matancha — Tue, 02 Aug 2011 12:43:52 GMT

Thank you Miha.

Thats exactly what I was looking for.

Regards
Matan.