Editing Key Expressions

gsherry1 · Post by **gsherry1** » Fri Nov 04, 2005 9:20 am

Hello Forum,

It seems like the ability to provide a constant value for one of the key columns in a lookup is not possible in PX, as it was in server jobs. It looks as though you are forced to only use input columns in the key expressions.

Is this true?

Thanks,

Greg

ray.wurlod · Post by **ray.wurlod** » Fri Nov 04, 2005 3:08 pm

Presume you're using a Lookup stage here. Are you on 7.5 or an earlier version?

gsherry1 · Post by **gsherry1** » Mon Nov 07, 2005 9:00 am

ray.wurlod wrote:Presume you're using a Lookup stage here. Are you on 7.5 or an earlier version?

Version 7.5x2.
Yes to Lookup Stage.

ray.wurlod · Post by **ray.wurlod** » Mon Nov 07, 2005 1:38 pm

Then, yes, it's true within the Lookup stage. But you could, of course, precede the Lookup stage with a Column Generator stage that uses a cycle from N to N for the column in question (one way to generate a constant).

gsherry1 · Post by **gsherry1** » Mon Nov 07, 2005 2:22 pm

Thank you for your response.

ray.wurlod wrote:Then, yes, it's true within the Lookup stage. But you could, of course, precede the Lookup stage with a Column Generator stage that uses a cycle from N to N for the column in question (one way to generate a constant).

Currently, I precede with transformer that appends the constants to my other primary link fields. Your solution sounds more complicated. Is is faster than using a transformer?

vmcburney · Post by **vmcburney** » Mon Nov 07, 2005 5:48 pm

Column Generator will definitely be better then a Transformer unless you are using that Transformer for a range of other tasks (such as constraints and derivations) and it is more convenient to leave it in. The stage is not that complicated to use.

Better yet if your lookup source is a database put your constant into the database stage as a where clause using the parameter # values. This will reduce the number of rows your lookup stage needs to prepare.

If your source is not a database but is a dataset or sequential file you could send it to a filter stage using the constant to filter the lookup data, this will reduce the amount of memory your lookup stage uses and speed up the lookups with a small overhead on startup.