Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
abe786
Contributor III
Contributor III

How to get sum of rows instead of expression total in an expression

Hi,

In my data model, I have a many to one relationship between 2 tables which is causing an issue that whenever I do a sum, it doesn't return a correct value if I do expression total, however I get correct value if I do sum of rows.

But I need it in an expression for which I need to use aggr to achieve the result, i.e. aggr the sum on all the columns of table1 first and then doing the sum on the expression.

See  attached code for better clarity of the issue.

But I'm facing performance issue in my application and was wondering if I can optimize the calculation anyhow either through data model fix or some expression not using aggr.

So far i'm not successful with it, if anyone can suggest alternate solution, it will be helpful.

Regards,

1 Solution

Accepted Solutions
zhadrakas
Specialist II
Specialist II

Hello,

you can edit your script to join both fact tables. Then sum(Value) will work as expected,

Fact1:
LOAD * Inline
[F1, F2, Key
A, B, 1
A, C, 1
A, D, 2
]
;

left join
LOAD * Inline
[Key, Val
1, 10
2, 20
]
;

regards

tim

View solution in original post

7 Replies
zhadrakas
Specialist II
Specialist II

Hello,

you can edit your script to join both fact tables. Then sum(Value) will work as expected,

Fact1:
LOAD * Inline
[F1, F2, Key
A, B, 1
A, C, 1
A, D, 2
]
;

left join
LOAD * Inline
[Key, Val
1, 10
2, 20
]
;

regards

tim

jonathandienst
Partner - Champion III
Partner - Champion III

Not sure I follow your requirement or your problem, but if you aggregate over a field with 3 values (F2), you will be adding 3 virtual rows. which will double count the value 10. If you aggregate over F1, you will get the "correct" total of 30.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
hemanthaanichet
Creator III
Creator III

Hi,

pls check the my attachment

Regards

Hemanth

dineshm030
Creator III
Creator III

Hi,

The Key column should be UNIQUE values or make UNIQUE values column as Key. Then only you can get the correct values.

ElizaF
Creator II
Creator II

Hello,

If I understand your requirement, another solution is  to use "applymach" function in edit script.

See below my solution:


Fact2:

Mapping

LOAD * Inline

[Key, Val

1, 10

2, 20

];

Fact1:

LOAD

ApplyMap('Fact2',Key,0) as Val,

*

;

LOAD * Inline

[F1, F2, Key

A, B, 1

A, C, 1

A, D, 2

];

abe786
Contributor III
Contributor III
Author

What Tim has mentioned is correct, which I figured myself also in next couple of days. Although your solution is also correct Eliza, but I have more than two columns in the mapping table in actual app.

ElizaF
Creator II
Creator II

Hi Abeer,

It would not be no problem in this case (than two columns in the mapping table). See below the example:

Fact2:

Mapping

LOAD

Key,

Val1&'#'&Val2;

LOAD * Inline

[Key, Val1, Val2,

1, 10, 50

2, 20, 160

];

Fact1:

LOAD

subfield(ApplyMap('Fact2',Key,0),'#',1) as Val1,

subfield(ApplyMap('Fact2',Key,0),'#',2) as Val2,

*;

LOAD * Inline

[F1, F2, Key

A, B, 1

A, C, 1

A, D, 2

];


I prefer APPLYMAP function versus with JOIN or LEFT JON.