Error While using Select Function
Hello,
In the attached model, I am trying to extract the data in the Amount column of Sample import to the commitment column of LayoutofMonths.
My formula is:
Commitment = select(Sample Import::Amount,@Sample Import::JobCodes,@Row) using Sample Import::Period as Month
I am still getting the #Size error
What do I need to change,in terms of model,layout or formula to get this correct.
Regards
ShriShank,
I looked over your model. There are a number of issues.
The categories Department and Dept L2 were not linked.
There was a problem with the values list (amount) and the key list (not sure so I used S No). There was not a one-to-one relationship between the value list and the key list which I traditionally refer to as the value/key list pair.
The way SELECT works is that for every value there must be one and only one possible key. This is true in Excel Vlookup too. In your case, the unlinked department category was creating the possibility that there were two amounts it could return; hence, the #SIZE error.
You can quickly tell if you have a pairing problem by using the dependency inspector and examining each argument of the SELECT function. In your case, I fixed that by making the value list and the key list "look through" all of the project category since that category was not in your Layoutof Months matrix. It was "unaccounted for" and therefore needed to be included in the SELECT statement.
So the plain lanquage way of working out a SELECT function is to say: Select a [b:2acvg3iz]value[/b:2acvg3iz] where the [b:2acvg3iz]key[/b:2acvg3iz] is equal to the [b:2acvg3iz]lookup[/b:2acvg3iz]. The bold words are the arguments to the SELECT statement.
I hope this helps. Mastering the SELECT and USING AS functions opens a whole wide range of modeling possibilities.
Good luck
Steven W. Bailey
Managing Director
BlackBriar Advisors LLC
3131 McKinney Ave, Suite 600
Dallas, TX 75204
Office: 214.599.8600
Cell : 207.650.8095
sbailey@blackbriaradvisors.com
http://www.blackbriaradvisors.com
http://www.blackbriarfpa.com