11 Replies Latest reply: Aug 3, 2015 9:17 AM by Friedrich Hofmann RSS

    Add info to a pivot table

    Friedrich Hofmann

      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

        • Re: Add info to a pivot table
          Friedrich Hofmann

          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.

            • Re: Add info to a pivot table
              Friedrich Hofmann

              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 ...

                • Re: Add info to a pivot table
                  Friedrich Hofmann

                  ... 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 ...

                  • Re: Add info to a pivot table
                    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.

                     


                      • Re: Add info to a pivot table
                        Friedrich Hofmann

                        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.

                          • Re: Add info to a pivot table
                            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.

                              • Re: Add info to a pivot table
                                Friedrich Hofmann

                                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 ...

                                  • Re: Add info to a pivot table
                                    Friedrich Hofmann

                                    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

                                      • Re: Add info to a pivot table
                                        Friedrich Hofmann

                                        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

                                          • Re: Add info to a pivot table
                                            Colin Albert

                                            What are the AD entries for your DSC in the QMC? Is there just one entry?

                                            How does the user list in the QMC compare to the XML data?

                                             

                                            You may need to create a mapping table to link the users from Qlik to the HR accounts and manage this manually!

                                              • Re: Add info to a pivot table
                                                Friedrich Hofmann

                                                Hi Colin,

                                                 

                                                strange: When I open this "assigning-dialog" in the QMC and search for those two names that I have tested, I can find only one instance of each name there - still in the xml file there are two.

                                                So maybe there is some obsolete old data in the xml file?

                                                I will try deleting that so that a fresh one is written next time round and I'll see. But I cannot do that myself as the server is oob (out of bounds ;-) for me, and the colleague who can do so is gone for two weeks ...

                                                What triggers the QMC to write a new copy of that file anyway? Is that done automatically, or only every time something about the allocation of licenses is changed?

                                                 

                                                Thanks a lot!