Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
matancha
Contributor

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:

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!

1 Solution

Accepted Solutions
Not applicable

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.

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

4 Replies
Not applicable

How to load 2 rows to 1 row according parameter

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

matancha
Contributor

How to load 2 rows to 1 row according parameter

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.

Not applicable

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.

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

matancha
Contributor

How to load 2 rows to 1 row according parameter

Thank you Miha.

Thats exactly what I was looking for.

Regards
Matan.

Community Browser