How to display data of Aggr() function?

[flagamba]

Hi Community,
I'm learning QLIK Sense and I have some problems...

#1 How to display raw data of Aggr() function ?

I read the answer that says to try the use of concat.
Somehow it works, but it is a very old answer (2012)

So I ask if in the meantime a better method has been invented.

#2 
I also ask if it is possible to display a full table  in the preview of datamanager,
without limits on the n.ro of rows,  possibly by pagination and with the desired order.
Okay, I can do it with a dedicated sheet, but it's a very boring method, in my opinion.

Any help is welcome.

Thank you all

[Andrei_Cusnir]

Hello,

Regarding your first question:

Aggr() functions returns multiple values, which is the reason why you can't display them as raw data. You can either combine all the values e.g. sum the values to have a single value (which is not what you need) or you can concatenate the values with Concat() function, as you are already aware of. 

Here is some additional information:

1. You have the dataset:

2. If you add Aggr() function the output would be:

3. So if you try to display the raw data as it is, it won't work, but concatenating the data will:

The Concat() function is the suitable for this use case scenario as it also allows you to provide the separator between values e.g. Concat(Aggr(NODISTINCT Sum(Value), Product), ', ')

The post that you have read, might be old but the solution provide seems to be applicable for your use case scenario.

I hope that this information was helpful. In case I have misunderstood the use case scenario, please elaborate in details by providing additional information. However, if it has helped you resolve the issue, addressed your concerns or at least pointed you in the right direction, please mark it as Accepted Solution to give further visibility to other community members. 
 

[Or]

Just adding one thing to that thorough (and correct) post - you actually can display aggr() as is, and oddly, it won't behave as though you used Only(Aggr()) as one might expect, which would return the same result for each row if one exists. It will instead display the result of the Only() value but only for the first row in the aggr() grouping. For example:

Load 'A' as Dim, RecNo() as Value Autogenerate(10);

Just one of those slightly odd things which I've never found an actual use for, but which behave differently from what I'd expect.

[Andrei_Cusnir · Accepted solution]

Regarding your 2nd question, in general it is suggested to keep different questions in separate posts, to avoid having long conversation regarding different things under a single post as it might get confusing.

However, if my understanding is correct, you are looking for a way to display more rows while loading data in Data Manager. As you have noticed:

There is a limitation on the preview data to improve performance of the software. If you have millions of data values in your dataset and you try to preview them all at the same time, it will consume a lot of resources and it will reduce the performance of the software. Keep in mind that during the import you are able to preview the data from a connection but from various tables at the same time.

As a workaround, you can import the data but not hit the "Reload" button yet. Selecting the loaded table you will have another preview on the bottom, which should show you all the loaded values.

I would recommend raising a feature request [1], if you would like to have this option in Data Manager preview as well.

I hope that this information was helpful. In case I have misunderstood the use case scenario, please elaborate in details by providing additional information. However, if it has helped you resolve the issue, addressed your concerns or at least pointed you in the right direction, please mark it as Accepted Solution to give further visibility to other community members. 
 

---

[1] https://community.qlik.com/t5/Knowledge-Base/How-To-Submit-Feature-Requests-For-Qlik-Products/ta-p/1...

[flagamba · Accepted solution]

Hi Andrei,
I really appreciated your answers to my questions.

I would like to clarify that the reason why I asked for the visibility of the data generated by Aggr() is that if I write a complex expression that acts on such data, I would like to see the intermediate results first, to understand if there is a bug in the expression, which often happens to me, unfortunately ...

That said, I tried to reproduce the example with Prod1, Prod2, Prod3 ... and I can't get the data to appear as in your figure (1) e fig. (2)
How to show all the rows without any aggregation as your fig. #1 does?
In fact, unless you have a hidden dimension, QLIK automatically applies Only() to the Value measure and shows me only Prod2 that appears with only one value.

For Prod1 and Prod3 it does not show the values.

See attached image #1

But, if I use Id as Dimension ... See Attached Image #2
Is this the Trick ???

How can I achieve the same result as you?

I noticed a magnifying glass in your figure (1): Are you using Value as Dimension ?

Thank you 

[Andrei_Cusnir · Accepted solution]

Hello,

Yes in my case I am using dimension, not measure. So, essentially, what I have is (for this test):

Data load editor:

load * inline [
Product, Value
Prod1, 20
Prod1, 30
Prod2, 40
Prod3, 50
Prod3, 60
Prod3, 70
];

Then, I have created a table with:

• Dimension: Product
• Dimension: Value
• Measure: =Aggr(NODISTINCT Sum(Value), Product)

This is to showcase you the data I have and how the Aggr() function works in straight table use. This is why the first two columns have magnifying icon.

I hope that this information is helpful.