How to create prompt based on two column values

Question

Hi,

I have two columns Category A and Category B as below. Requirement is to create one prompt to let user choose 0 from Category B, 1, 2, 3 from Category A.

Two challenges we are facing here:

1) Category B 0 is part of Category A

2) Category B "Unspecified" could be in Category_A 1, 2 or 3.

Any suggestions would be much appreciated.

cesar.advincula.o · Answer

The approach that @Martin van Donselaar will work if all your category A and category B it is on the same subject area.

But if your model is little more complex MODEL you can use  PRESENTATION VARIABLES AND sql results.

For instance, you can define two prompts, one is the driving prompt and the other prompt show the results based on the selected values from driving prompt.

Dont know exactly your model is.

Martin van Donselaar · Answer

(Why) Does it need to be a single prompt? You can Limit prompts based on values from other prompts:

Robert Angel · Answer

Not the most performant but you could concatenate the content of the two rows using a SQL statement, populate a variable with the result and use substring to separate out the relevant parts of the variable for the filters for the two columns.

On your challenges assuming your data posted is representative I would only look at one character of column 'A' and character 2 to the end of the string for column 'B' and keep it all as text.