topic Re: How to group (or merge) 2 (or more) rows difference in pivot table with a large data? in App Development

How to group (or merge) 2 (or more) rows difference in pivot table with a large data?

Anonymous — Fri, 27 Apr 2018 03:46:57 GMT

Hi all.

I'm newbie in Qlik. I have a big problem.

I have a straight table with millions rows (more than 200 million rows).

As is:

ID	Name	City	Start_date	End_date	Sale
0101	Company A	New York	12/24/2017	1/17/2018	200
0101	Company A	Sydney	1/18/2018	1/22/2018	500
0101	Company C	New York	1/23/2018	1/31/2018	400
0101	Company D	Califonia	2/1/2018	2/25/2018	800
0101	Company E	New York	2/26/2018	3/5/2018	900

In Pivot table:

Case 1:

If End User select the date range from date: 1/18/2018 to date: 2/25/2018

I need to show on the pivot table as below:

ID	Name	City	Sale
0101	Company D	Califonia	1700

Case 2:

If End User select the date range from date: 1/18/2018 to date: 1/31/2018

I need to show on the pivot table as below:

ID	Name	City	Sale
0101	Company C	New York	900

So, how I do it?

Ps: I have tried to use Aggr() function but the performance is too slow because data is too much.

sunny_talwar — Fri, 27 Apr 2018 11:42:20 GMT

How did the Aggr() expression looked like? and although performance was an issue, was it working?

Anonymous — Wed, 02 May 2018 06:20:47 GMT

Hi Sunny Talwar,

Thanks for your support.

It worked but too slow.

Aggr() expression looked like:

#Dimension Latest for City

=Aggr(FirstSortedValue(Aggr(Concat(DISTINCT City, ','),Date),-Date),ID)

#Dimension Latest for Name

=Aggr(FirstSortedValue(Aggr(Concat(DISTINCT Name, ','),Date),-Date),ID)

sunny_talwar — Wed, 02 May 2018 16:32:13 GMT

Is this a calculated dimension or chart expression? If it is an expression, can you try these to see what you get?

FirstSortedValue(Aggr(Concat(DISTINCT City, ','),Date),-Date)

FirstSortedValue(Aggr(Concat(DISTINCT Name, ','),Date),-Date)

Anonymous — Thu, 03 May 2018 01:59:18 GMT

This is a calculated dimension and we apply it on pivot table (not on chart)

I tried as your suggest:

FirstSortedValue(Aggr(Concat(DISTINCT City, ','),Date),-Date)

FirstSortedValue(Aggr(Concat(DISTINCT Name, ','),Date),-Date)

But the error is as below:

Maybe the dimension does not accept this expression.

sunny_talwar — Thu, 03 May 2018 23:39:39 GMT

Why don't you try using it as an expression?