topic Re: SV:problem calling a subroutine from within a load in QlikView

problem calling a subroutine from within a load

Wed, 27 Apr 2011 14:01:20 GMT

Hello

I'm having a problem calling a subroutine.

I've created the subroutine:

SUB CheckPrice(pCustomer, pProduct, pSalesDate)
LOAD SalesPrice
FROM DateFile.txt
(txt, codepage is 1252, embedded labels, delimiter is '\t', msq)
WHERE
CustomerCode = '$(pCustomer)' AND
ItemCode = '$(pProduct)' AND
FromDate <= '$(pSalesDate)' AND
'$(pSalesDate)' <= ToDate
;
END SUB

When I call the subroutine once for a fixed customer/product/date it returns the price I expect.

However, I want to call the subroutine from within a LOAD. When I try the code below I get 'Error in expresssion:')' expected.

LOAD SalesDate,
CustomerCode,
ItemCode,
IF (ISNULL(Call CheckPrice(CustomerCode, ItemCode, SalesDate)), 'N', 'P') as chkprice
FROM SalesOrder.qvd
(qvd);

Any ideas why this is?

Cherian

SV:problem calling a subroutine from within a load

ToniKautto — Wed, 27 Apr 2011 14:09:21 GMT

Have you tried putting the Sub Call in a dollar expansion?

$( Call CheckPoint( ...

SV:problem calling a subroutine from within a load

Wed, 27 Apr 2011 14:22:49 GMT

Hello Toni

Tried that but it made no difference

SV:problem calling a subroutine from within a load

ToniKautto — Wed, 27 Apr 2011 16:10:20 GMT

Looking at your example I do not really see how it should work.

The subroutine will only execute a table load, but how does it return a value to trigger the IF conditional check for null? I imagine it would always be interpreted as a NULL.

Have you tried executing the CALL as string in the $-expansion?

$(="IF (ISNULL(Call CheckPrice(CustomerCode, ItemCode, SalesDate)), 'N', 'P')")

SV:problem calling a subroutine from within a load

rbecher — Wed, 27 Apr 2011 17:30:41 GMT

Hi,

what you're trying to achieve is a script function. This is not possible in QV, only sub routine without return values.

- Ralf

Re: SV:problem calling a subroutine from within a load

farolito20 — Thu, 10 Jan 2013 21:08:39 GMT

It's impossible call a subroutine in LOAD statement?

Re: SV:problem calling a subroutine from within a load

rbecher — Thu, 10 Jan 2013 21:42:15 GMT

Right. You can call a sub routine only via "call"..

Re: SV:problem calling a subroutine from within a load

farolito20 — Thu, 10 Jan 2013 21:48:51 GMT

Damn :s Thanks :s

About mapping tables and apply match, how do that if I pass more then one field?

For example, I have this

if(match(t_cuni,'LTA','FGS','QQ'), 1, applymap('fnConvToQQ',uniBase)) * t_qhnd as cantReturn;

but here, I just pass one field, unibase to mappiing fnConvToQQ'.

how pass two fields? like this?

applymap('fnConvToQQ',uniBase,cantBase)

Re: SV:problem calling a subroutine from within a load

rbecher — Thu, 10 Jan 2013 22:35:01 GMT

You can use applymap() multiple times in a LOAD statement. One for each field..