Help with creating wild card or other match type

[#1]

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.

[vishsaggi]

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

[effinty2112]

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

[#4]

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

[vishsaggi]

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.