Combined List Of Values From Two Columns

building semantic layer
1

Ok I need some help with this tricky one.

I have a table that has answers to questions in the fomat:

QSTN_ID | ANSWR_NUM | ANSWR_CHAR | ANSWR_DATE

Questions can have an answer that is either a date, or a number or a char.

Logic tells you that it should be only 1 type for each question but upon analysing the data I have discovered this not to be the case.

For example Question 1 - Base Income Type has a 0 in ANSWR_NUM or a 7 digit alphanum code in ANSWR_CHAR

Question 3 - Phone Number has a number in ANSWR_NUM or a number in ANSWR_CHAR

Question 16 - Max Steps has a 0 in ANSWR_NUM or COUP, FEW or N in ANSWR_CHAR

From what I can see so far if there is a response in either ANSWR_NUM or ANSWR_CHAR then ANSWR_DATE is empty or if ANSWR_DATE has a value the other two are empty so at least that is correct.

I want to create prompts for certain questions but want to have a single question and a single LOV that combines the possible values for both ANSWR_NUM and ANSWR_CHAR.

My first attempt was to just select both columns and create prompts with the same text eg Enter Max Steps, but this resulted in an error stating

So I changed the ANSWR_NUM object to select TO_CHAR(ANSWR_NUM) and changed the object type to Charatcer and saved and exported the universe.

Tried again and this time it displayed one prompt, but only the LOV for the first column got display ie it didn’t show a combined LOV.

How can i get a combined LOV to display, I’m thinking I might have to create a derived table in designer that combines the columns via a union or something.

Any help woould be greatly appreciated.

Using BO XI 3.1 SP2.

Thanks,

Nick

Nniixx :australia: (BOB member since 2009-09-02)

2

At ease everyone I did some Googling and fount that if I concatenate ANSWR_NUM and ANSWR_CHAR it works exactly how I want.

So basically created a an object that states

select ANSWR_NUM || ANSWR_CHAR

and then create one prompt using this object and it lists all LOVs.

Nniixx :australia: (BOB member since 2009-09-02)

3

Yes and you can also build that directly in the universe by editing the object you want, click properties, click edit, then add the objects you want to display with the LOV.
The object you would edit initially is the primary one that you want to use.
The click on display and refresh the LOV and you should see two columns in your LOV list.

plessiusa :netherlands: (BOB member since 2004-03-22)