9 Replies Latest reply: Jan 2, 2013 7:49 AM by Paul Damen RSS

    Rename blank fields

    Paul Damen

          Hi all,

       

      If have a list of users in Qlikview, they belong to a department (list comes out of the system, the department is a excel file which is loaded). Now the list from the system contains a lot of names with user that no longer work here, therefor they don't appear under the drill down group I made (department - user) because the people that no longer work here don't have a department.

       

      How can I make a field in the script that looks up users with that don't have a department and then name the field behind this user "Out of Service"? I tried several things but because the fields come from 2 different places I can't seem to find the formula to look in 1 and fill another.

       

      Regards,

      Paul

        • Re: Rename blank fields
          Henric Cronström

          If you load the users that exist (in the Excel file) into a Mapping table, you can use this later on to tag the users in a field "Status". Use the function Applymap for the lookup.

           

          ExistingUsers:

          Mapping Load User, 'Active' as Status From ExcelFile;

           

          Data:

          Load *,

                    ApplyMap('ExistingUsers', User, 'Not Active') as Status

                    From Database;

           

          An alternative approach is to use the Exists() function to determine whether this user has been loaded before (in the Excel sheet) or not.

           

          HIC

          • Re: Rename blank fields

            Dear,

             

             

            you can use Henric procedure.

             

            or

             

            write the following peice of code before the tables which your loading.

             

            Nulls_Map:

            Mapping Load

            Null(),' <Out of Service>'

            autogenarate 1;

             

            Map 'department'  Using Nulls_Map;

             

             

            I hope it will help you.

             

            Thanks,

            Mukram

              • Re: Rename blank fields
                Paul Damen

                Hi Mukram,

                 

                I tried you method but instead of filling the blanks it creates a new departement called Out of Service with no name in front. The fields behind the names of the user which are out of service are still showing -

                 

                Regards,

                Paul

                  • Re: Rename blank fields

                    Dear Paul.

                     

                     

                    did you tried Henric method?

                     

                    if still your not getting the expected result.

                     

                    then please can you attach a sample file.

                     

                     

                    Thanks,

                    Mukram.

                      • Re: Rename blank fields
                        Paul Damen

                        I tried his method as well but when I put the Apply Map in the SQL select I get the following error when I reload:

                         

                        "ErrorMsg: 'ApplyMap' is not a recognized built-in function name"

                         

                        The database part load looks like this, am I doing something wrong there?:

                         

                        SQL SELECT

                        "cm_leadscore", fullname, jobtitle, owneridname, accountidname, "new_contact_importancename", "new_contact_ratingname", parentcustomeridname, accountid, statecode , emailaddress1,

                        ApplyMap('ExistingUsers', owneridname, 'Not Active') as Status

                        FROM "*****_CRM".dbo.FilteredContact

                          • Re: Rename blank fields

                            Dear Paul.

                             

                             

                            I think you need, while loading the table FilteredContact from Database you need to select the Preceding Load

                            options.

                            so you can apply the ApplyMap Built in function in between load and sql select.

                             

                            For example:

                             

                            FilteredContact:

                            Load

                            "cm_leadscore", fullname, jobtitle, owneridname, accountidname, "new_contact_importancename", "new_contact_ratingname", parentcustomeridname, accountid, statecode , emailaddress1,

                            ApplyMap('ExistingUsers', owneridname, 'Not Active') as Status;

                            Sql SELECT ......................;

                             

                            i hope it will work it out.

                             

                            Thanks,

                            Mukram.

                              • Re: Rename blank fields
                                Paul Damen

                                It now gives me the error that there is a syntax error near the word FROM what is wrong there?

                                 

                                LOAD


                                "cm_leadscore", fullname, jobtitle, owneridname, accountidname, "new_contact_importancename", "new_contact_ratingname", parentcustomeridname, accountid, statecode , emailaddress1,

                                ApplyMap('ExistingUsers', owneridname, 'Not Active') as Status;

                                SQL SELECT FROM "****_CRM".dbo.FilteredContact;

                                 

                                  • Re: Rename blank fields

                                    Dear Paul.

                                     

                                    I think something wrong in our sql select statement .

                                     

                                    it should be not  like

                                    SQL SELECT FROM "****_CRM".dbo.FilteredContact;

                                     


                                    But it should have to have

                                    * before from.

                                    like as follows,

                                    SQL SELECT  * FROM "****_CRM".dbo.FilteredContact;

                                     

                                    or you should have the fields you are loading fom the database.

                                     

                                    Thanks,

                                    Mukram.