[Solved] Derived Fields in Framework Manager

[marksci]

Hi,

I am new to framework manager and need some assistance.Ã,  I have a field that I brought into my query subject from our database.Ã,  It is five characters long.Ã,  I want to create a second field that has a value that is based off the first 2 characters of the main field.Ã,  For Example I have a 5 position field called location and it contains 01111.Ã,  I need a second field that says if the first 2 positions of location is 01 than that is New Jersey.Ã,  There will be more location numbers and values of course.Ã,  I know this is probably really easy so any help would be greatly appreciated.

Thanks

MS

[sir_jeroen]

This can be done in different ways:
1. Create a database Query Subject (QS) and add a calculation with the expression:

Ã,  Case
Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  when (left(<field>;2) = '01') then ('New Jersey' )
Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  when left(<field>;2) = '02') then ('Rotterdam' )
Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã, ...
Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  else ('....')
Ã,  Ã,  Ã, end

Now your database QS has this expression.

2. Create a standard Database QS on which you create a Model QS in which you add the above case when ... then ... .

I would recommend to use method 2 because now the expression is only executed when the calculation is used.


By the way:
Regarding building Framework packages: if possible try to attend the cognos course: Metadata Modelling pt. 2. I followed this course and it clarified a lot to me and made building packages more understandable.
Let me know if this worked.

[marksci]

Thanks that did the trick.

[sir_jeroen]

You're welcome...
I'm curious: Which method did you choose?

Good luck!!!

[marksci]

I used method 2.

[sir_jeroen]

Ok... Tnx for your reply.