Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
datanibbler
Champion
Champion

Add info to a pivot table

Hi,

I have a question - probably quite a simple one, but I haven't got much experience with pivot_tables.

I have the app >> LicenseAnalyzer << which you can find in the Community, here

_Analyser_K3DS

There is a pivot table on the sheet >> DocumentCalMatrix << - well, several.

I would like to add some more info to that, I just don't know where as  both are so nicely made up as Crosstables.

I have the info which plant and which team the holder of a licence is assigned to. That would make the whole thing somewhat more practical still, but, well, obviously I cannot add it as dimension (although it should not be a problem, I have cleansed the data coming from our HR system so that every employee has an association with only one plant and one team.

But it's obviously not working, I'm doing something wrong.

Can somebody advise me here?

Thanks a lot!

Best regards,

DataNibbler

11 Replies
datanibbler
Champion
Champion
Author

I don't get it ...

I have built it into the dimension now (like >> = UserName & '/ ' & Team <<) and it works - for some employees, but not for others.

The only difference I can see is that some UserNames are present twice - that is okay because they have been assigned two licenses, but they still have only one team and one plant. For those, it works fine.

<=> Some others (I have tested two) have also been assigned two licenses, but for some reason they are present only
        once. Also, even though one plant and one team is associated with them in QlikView, it doesn't work for them.

datanibbler
Champion
Champion
Author

Very strange - I have continued some testing with those and it seems that those emps where this formula does not work - I don't know, the chart tells me they have been associated two licenses, but when I select them in a listbox on another sheet and just make a textbox with the >> sum(#UserDocCals) <<, that tells me they have not been assigned any ... so something about the link must be weird ...

datanibbler
Champion
Champion
Author

... ok - the QMC tells me one of those emps has one license assigned 😉 So it seems I need a new file. And then there is something wrong in my app, too.

P.S.: Can I edit that line in the .ini file (or rather, have it edited) so that QlikView puts that xml file somewhere else than in what seems to be the default directory? The thing is, that is on the company server which I don't have access to, and whenever I need something that is on the server, getting access is a mayor task ...

Colin-Albert

How have you added the extra fields to your data model?

Have you linked another table to the UserName(Full) field, or added the data as extra columns to the Cals table via an applymap?

Either way should work OK as extra dimensions on the charts.


datanibbler
Champion
Champion
Author

Hi Colin,

I have it in an extra table. Yes, the link obviously works fine, otherwise there would be no emps with the correct info displayed, but there are some ...

I have used the UserName(Short) as link between the tables. I will try with the other one, maybe that makes a difference.

I have just noticed that my file is NOT outdated as I first thought, I looked at the wrong app in QMC ... that one emp does have two licenses, the chart shows it correctly. The question is just why my textbox shows 0 - and the emp's plant and team do not appear. Well, that name must be somewhat different. I will look again.

Colin-Albert

It would be better to link on the username(full) as this combines the domain and username.

A user could have logins in different domains but with the same shortname.

datanibbler
Champion
Champion
Author

Hi Colin,

no, the domain is always the same.

But I have now found the reason: There are duplicates in that xml file, for whatever reason I don't know:

- One emp is written with a "CZ" at the end, but that xml file knows him with this AND with just a "C" - and the
   latter has the two licenses while the former does not have any ...

- Another emp actually has an "ü" in his name, but that xml file knows him also with a "ue" in the name ...

What can I do there? I don't know why QlikView generates these duplicates - well, it must be in our ActiveDirectory, I guess, but then that does not correspond with our HR system because that one does not have duplicates.

But how can I go about this? It's nigh impossible to foresee all possible combinations ...

datanibbler
Champion
Champion
Author

Ahaaa ...

The AD is populated independently from the HR system. There can be typos or anything - and apparently, people can have their name changed - without the outdated version being deleted for whatever reason ...

So I'll make another trip to the IT_helpdesk and ask them to do a little cleanup in the AD ... the chances of that happening are - oh, I see a dog flying by there. No rain, he's flying high  ...

So any ideas on how to deal with this possibility would be welcome.

Thanks anyway!

Best regards,

DataNibbler

datanibbler
Champion
Champion
Author

Hi,

I have to go about this step by step. So:

- Does the QMC really pull its data from the AD for the allocation of licenses? How is that dialog populated?

- What triggers the QMC to generate a new .pgo or .xml file?

- Could it be that the file is not overwritten, but only changed so that there could be old entries in there that were in the
   AD once upon a time, but are not there anymore now?

Thanks a lot!

Best regards,

DataNibbler