AGGR function needed?

[LuisMa]

Dear all,

I'm kinda new to QlikView development and I recently took a huge project of someone that left the enterprise. He knew a lot of QlikView but he's now gone, and the enterprise realized far too late that he didn't teach anyone how to do the maintenance in his files.

Anyway, I have a table with a training code and the driver code.

I have another table with the driver code and the distance he drove.

I need to connect those tables to COUNT the number of drivers which have the traning code = 0801 and have distance > 0.

I will appreciate any help in this matter.

Please see the attached screenshot to see the QlikView generated table.

In this case, I need a function that shows me a total of 1 driver (the A18835847, because he has training code 802 and has KMs > 0).

Thanks in advance!

[jensmunnichs]

The simplest answer I can give you is to create a text object with the following function (note that you might have to change some field names and that I used training code 0801, as that is the requirement you mentioned initially):

=Count({<_km = {">0"}, Training_code = {0801}>} [Driver_code])

Or, if you know you'll be interested in the count for other training codes in the future as well, create a chart using Driver_code as dimension and the following as your expression:

Count({<_km = {">0"}>} [Driver_code])

However, the main issue seems to be that I don't think your data is linking properly, judging by the null values in the table you posted. You might have to change the formatting of your data fields in script, but it's hard to tell what the exact problem is without being able to open your application. Would you be able to post a copy of your application (possibly after removing/scrambling sensitive information)?

Either way, for more information on how to create functions like the above, look for 'set analysis' on Google (or on here, which is where you'll most likely end up anyway).

[LuisMa]

Hi Jen,

The formula I have working is

count(DISTINCT{$<Training_code={'0802'}>} chaufeur_code)

But it brings me all the drivers with this Training Code, not the drivers with distance > 0

And when I try to insert the distance in the Set Analysis it simply doesn't work because the tables aren't connected.

Thanks for your help, but it didn't work. I have already tried it many times and in a certain number of ways.

No problem with the names of dimensions, I think it is a simple example with 3 informations in 2 tables which need to be connected.

If you have any other thought, I'll be happy to know it!

[jensmunnichs]

Once again, if you could post your application I'm a lot more likely to be able to help you. Or, at least post the part of your script that loads this data into the application.

The problem most likely isn't that the field names aren't the same, but rather that the data within the field names isn't formatted the same way, which means that values that look the same aren't actually connecting (for example, if one is formatted as a number and the other as text). To confirm this, when you look at the table viewer, can you see a connection like below? And, when hovering over the drivercode fieldname, what does it say for subset ratio?

EDIT: Forgot to add the image:

[LuisMa]

Yes, they are connected.

Yes, the names are the same.

Yes, the field names are formatted the same way.

I don't think I would be able to share part of this file, but if we can't find a solution without it, I'll find a way..

[jensmunnichs]

Hi,

Just to make sure I've got this right, the distance field is in MST_ZCHO... (and not in the GPS table)? I'm sorry if it seems like I'm asking dumb questions, it's just very hard to understand what's going on here without the actual application (and yes, I know I'm repeating myself here lol).

Would it be possible for you to post the parts of your script that load the relevant tables, and maybe a very small sample of those tables? You could modify your script slightly by adding 'FIRST 10' before your load statement and storing this table as a qvd (STORE [tablename] into [tablename].qvd (qvd) ). It would also help if you could tell me the actual names of the fields we're talking about here, as I think you changed some of them in your original post so it's hard for me to relate your image to your original post.

[Brett_Bleess · Accepted solution]

Well, not sure this will help, but I figured it was worth a shot, check the following Design Blog post, there are some further links at the end of it, just FYI, and there are some other AGGR related posts as well if you get back up to the Design Blog portion of the URL, you can search yourself there and check out the other posts.

https://community.qlik.com/t5/Qlik-Design-Blog/Don-t-get-aggr-avated-using-aggr/ba-p/1464136

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog  (This is the main Design Blog link where you can search)

Regards,
Brett