topic Re: Lookup challenge ( lookup() function or otherwise ) in Administrative Discussions

Lookup challenge ( lookup() function or otherwise )

RADSr — Wed, 17 May 2023 17:21:40 GMT

I have a list of products to which I want to assign categories and I'll want to repeat this process for multiple iterations.

So I can build an insight:

PRODUCT, <formula column w/ category text>

The challenge is that I want to pick the category name from a list and then populate the formula column with the category *code.* I'd use a lookup for something like that, but since the product list doesn't have a category ( or code ) associated with it I'm kind of stuck.

I have tried lookup(category_name, $category_key, 'NA' ) but the lookup function is barking at me because it seemingly does not like session variables as the input key value.

I have an idea, but it's quite kludgy and I'm hopeful our Community has something elegant up their collective sleeves.

Thx!

Re: Lookup challenge ( lookup() function or otherwise )

RADSr — Thu, 18 May 2023 15:07:24 GMT

OK - until something better comes along here's what I've done. 1) Created a formula column in the Incorta table concatenating name and code w/ a pipe delimiter NAME|CODE which makes the presentation variable easy to read and sortable alphabetically. 2) Created a presentation variable in the dashboard using the formula column. 3) Created formula column in the insight:

trim(

substring(

$PV_NameCode,

(find(

'|',

$PV_NameCode

) + 1),

255

)

Not mentioned in the OP - but really cool - is that this will enable me to pull data from an ERP, examine and segregate data, assign data new attributes, and then push the new attributes back to the ERP ( I'm thinking using a flat file to a data destination for that last bit ).