topic Re: Add a column with nested source_lookup in Qlik Replicate

Add a column with nested source_lookup

RonFusionHSLLC — Thu, 30 Dec 2021 22:50:59 GMT

In Replication, I need to synthesize a column from a nested source_lookup function...doesnt seem to work; or did I do it wrong...?

Trying to add a column to a table called AccountDetails, and fill it from a lookup function.

The table has a column called AccountID (fk to Account.ID)

The table Account, has a column called OrgID (fk to Orgs.ID)

The table Orgs has a value AccountNumPfx, I want populate the value of a new column with AccountNumPfx

Something like this...

The inner source_lookup picks up the ID field from Orgs.ID using $AccountID from the current table, passes that back to the outer source_lookup, which returns the AccountNumPf

source_lookup(10000,'dbo','Orgs','[AccountNumPfx]','[ID]=?',source_lookup(10000,'dbo','Account','[OrgID]','[ID]=?',$AccountID))

Ron

Re: Add a column with nested source_lookup

Heinvandenheuvel — Fri, 31 Dec 2021 04:43:10 GMT

No can do. No nesting. And you really don't want to do this anyway if performance is of any concern. You Don't want to send a singleton select to the source DB twice when conceptionally one singleton joined lookup will do.

Documentation (12 Customizing tasks, Data Enrichment input parameters) states for 'EXP' : "Note: The expression syntax must be native to the endpoint it accesses.". Well, source_lookup is native to Replicate, not to the DB.

Your best, cleanest, bet is a VIEW on the source to join Orgs and Account

*untested* You might try to create the join in table/expression arguments by specifying 'Orgs, Account' for TBL and something like 'Orgs.ID = Account.OrigID AND Account.ID = ?' for EXP.

hth,

Hein

Re: Add a column with nested source_lookup

RonFusionHSLLC — Fri, 31 Dec 2021 11:02:08 GMT

I see. Good to know, totally makes sense.

A follow up question...

Can Replicate call a sp or function?

Re: Add a column with nested source_lookup

Heinvandenheuvel — Fri, 31 Dec 2021 15:29:41 GMT

>> Good to know, totally makes sense.

The query arguments in the source_lookup statement are used to create a SQl statement using simple text replacement. Like a sprintf.

Replicate simply creates "SELECT EXP FROM CHM.TBL WHERE COND;"

You can verify that with with verbose logging (just use a 1 table, 1 row test case) as well as in the database SQL cache.

>>Can Replicate call a sp or function?

Yes - it indicates EXP for Expression.

Hein.

Re: Add a column with nested source_lookup

RonFusionHSLLC — Fri, 31 Dec 2021 16:19:06 GMT

Nice...thanks for the tip