topic Re: How to aggregate more rows in a straight table in QlikView

How to aggregate more rows in a straight table

arnould_it — Tue, 14 Nov 2017 06:27:49 GMT

Hello to everyone,

I've to aggregate more rows by the same TimeStamp, now exposed as shown below:

The 2 intermediate rows have to be unified in just 1 row.

Can I do this directly in the table? or I've to do this using script?

Thank you in advance

Alessandro

Re: How to aggregate more rows in a straight table

Anil_Babu_Samineni — Tue, 14 Nov 2017 06:31:30 GMT

Either is possible in script/UI. You already did that in image which you showed. Can you explain little more on your needed

Re: How to aggregate more rows in a straight table

arnould_it — Tue, 14 Nov 2017 07:11:22 GMT

It would be better to solve it in the UI.

As written I've to aggregate the 2 intermediate rows you can see in the image above, avoiding all null cells.

Which is the correct syntax?

Any suggestions?

Re: How to aggregate more rows in a straight table

tresB — Tue, 14 Nov 2017 07:14:21 GMT

How (statement/expression used) have you created the TimeStamp_mm field in the script? Could you share your sample app?

Re: How to aggregate more rows in a straight table

shraddha_g — Tue, 14 Nov 2017 07:15:22 GMT

try

sum(Aggr(Sum({<element = {'EE1'}>}VarValues),TimeString_mm))

and

sum(Aggr(Sum({<element = {'EE2a'}>}VarValues),TimeString_mm))

Re: How to aggregate more rows in a straight table

Anil_Babu_Samineni — Tue, 14 Nov 2017 07:17:30 GMT

Perhaps this

sum(Aggr(Sum({<element = {'EE1'}, VarValues = {"=Sum(VarValues)>0"}>}VarValues),TimeString_mm))

Re: How to aggregate more rows in a straight table

arnould_it — Tue, 14 Nov 2017 07:26:29 GMT

unfortunately none of suggestions work

Re: How to aggregate more rows in a straight table

Anil_Babu_Samineni — Tue, 14 Nov 2017 07:27:59 GMT

Can you make input and result set of data?

Re: How to aggregate more rows in a straight table

shraddha_g — Tue, 14 Nov 2017 07:28:10 GMT

Provide sample app

Re: How to aggregate more rows in a straight table

arnould_it — Tue, 14 Nov 2017 07:32:06 GMT

TimeString_mm is the minutes-truncate timestamp string I've created in the script to aggregate (in the UI) many rows in just 1 row, but I don't know how to complete the action.

Alternatively I could do this in the script using a distinct function but it would be better in the UI because it's more dynamic.

Re: How to aggregate more rows in a straight table

tresB — Tue, 14 Nov 2017 07:39:24 GMT

What if you use calculated dimension instead of the the field? Try expression like:

Timestamp(Floor(TimeString_mm, 1/24/60), 'DD:MM:YYYY hh:mm')

Re: How to aggregate more rows in a straight table

arnould_it — Tue, 14 Nov 2017 08:12:11 GMT

I've provided to upload a sample app, just waiting for the approval.

Menawhile you can see below another screenshot.

The red squared rows have to be aggregated in just 1 row:

Re: How to aggregate more rows in a straight table

tresB — Tue, 14 Nov 2017 08:31:40 GMT

Have you tried the way as I suggested above using floor() ?

Re: How to aggregate more rows in a straight table

arnould_it — Tue, 14 Nov 2017 08:55:51 GMT

Hi Tresesco,

I've tried and it seems working well, I'll apply it on the entire app and test all.

Thanks a lot

Alessandro