4 Replies Latest reply: Sep 29, 2017 12:02 PM by Vishwarath Nagaraju RSS

    Help with creating wild card or other match type

    Ian Lemle

      Hi,

       

      I am new to working with Qlik and am hoping someone might have an idea on how to help.

       

      I am developing a report that I can use to share donation/payment information with specific groups that my office reports out to.

       

      Background: I work for a graduate school within a large university. Within my school are ten plus departments. Any time a donation or payment is made it is applied to an Account. Each department has several accounts, and none of the accounts are tagged with the department. All accounts have a static opening string of numbers associated with a specific department - 345.89034.xxxx.xxxxxx.xxxxx

       

      Desired result:

      Using a list box with all the department names, I would like to be able to select the department name and have my table show all the donations and payments made to that department so I can export them and share them with the intended recipients. This can't require too much effort on the part of the end user as it needs to work after I am long gone.

       

      What I need to do:

      1) Load a data set with the department names.

      2) in Qlik, connect the department names with their specific account numbers. I need to use a wild card statement of some sort to match the department to the first few digits of the Account string (these never change even if a new account is created for that department).

      3) I need my table to show only the gifts associated with that department when I select the department from the list box.

       

      I will issue an advance apology, but I cannot share a data set owing to the confidentiality of the data being used.

       

      Any thoughts or suggestions are appreciated.

        • Re: Help with creating wild card or other match type
          Vishwarath Nagaraju

          Can you mock up some dummy department names and the numbers and your expected output to be?

          • Re: Help with creating wild card or other match type
            Andrew Walker

            Hi Ian,

            Try something like this. Load a table relating the number code of each department to the department name.

             

            Department:

            Load

            DeptNo,

            Department

            From ...

             

            You can load it from an excel sheet or enter the lines into an inline table.

             

            When you load your gift data include a line that creates the field DeptNo.

             

            Gifts:

            LOAD

            .

            .

            Text(Left(AccountNumber,9)) as DeptNo,

            .

            .

            From ....

             

            Any records of a gift for account 345.89034.xxxx.xxxxxx.xxxxx will have a value of 345.89034 in field DeptNo.


            I used the Text function to stop DeptNo being interpreted as an actual number. Make sure that when creating the Department table the field DeptNo is a string, use Text() if you have to.

             

            Now you will have a dimensional table Department that will be associated with records in your Gift Table.

             

            good luck

             

            Andrew

            • Re: Help with creating wild card or other match type
              Ian Lemle

              Hi,

               

              I can certainly attempt to mock it up.

               

              Seperate from the data is the departments: English, History, Science

               

              The data set includes a lot of extra information, but it boils down to this:

              ENTITY ID     ENTITY NAME     GIFT AMOUNT            ACCOUNT

              0001234567     Bob Johnson          $2,000                    345.45678.432587.9004.8888

              0004356722     Sally Jennings        $100                      345.45678.875904.9006.9999

              0005567321     Dale Billings           $300,000                432.84256.890769.0098.1111

              0006743213     Albert Molar            $50                       432.84256.124356.9990.7789

               

              The department information is not currently tracked in the system, but you know that all History accounts start with 432.84256.xxxx and all Science accounts start with 345.45678.xxxx. The solution needs to work with multiple accounts for each department.

               

              Thanks

                • Re: Help with creating wild card or other match type
                  Vishwarath Nagaraju

                  May be create a department name from the Account like

                   

                  LOAD ENTITYID, ENTITYNAME, GIFTAMOUNT, ACCOUNT,

                             IF(Left(ACCOUNT, 9) = 345.45678, 'History',

                             IF(Left(ACCOUNT, 9) = 432.84256, 'Science',

                             IF(Left(ACCOUNT, 9) = somenumber, 'English',

                             ....... ))) AS Department

                  FROM yoursource;

                   

                  Something like above will create a department and use this new Department field for selections.