Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Highlighted
vinhnguyen
New Contributor

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

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:

IDNameCityStart_dateEnd_dateSale
0101Company ANew York12/24/20171/17/2018200
0101Company ASydney1/18/20181/22/2018500
0101Company CNew York1/23/20181/31/2018400
0101Company DCalifonia2/1/20182/25/2018800
0101Company ENew York2/26/20183/5/2018900

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:

IDNameCitySale
0101Company DCalifonia1700

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:

IDNameCitySale
0101Company CNew York900

So, how I do it?

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

1 Solution

Accepted Solutions

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

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

5 Replies

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

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

vinhnguyen
New Contributor

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

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)

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

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)

vinhnguyen
New Contributor

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

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:

IMG_error.PNG

Maybe the dimension does not accept this expression.

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

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