topic Re: Calling Subs within Loads to return value in QlikView

Calling Subs within Loads to return value

tom2qlik — Mon, 24 Apr 2017 09:52:35 GMT

Hi All,

I am working on a solution and have put the pseudo code below:

Subroutine 1

Define vResc as Number

ApplyMapLOT Site

Define vSite as Site

Subroutine 2

Define vResc as Number

ApplyMapNUMBER Site

Define vSite as Site

If Number = vResc then vSite

else Call Subroutine 1 (returns vSite)

else Call Subroutine 2 (returns vSite) as Site.

My aim is to give all the same numbers the same site regardless if a lot exists or not. Desired outlook is in the table.

Subroutine 1 does the applymap on the log to return the site. It also assigns the number to a variable and the site to a variable.

Subroutine 2 does the applymap on the number to return the site. It also assigns the number to a variable and the site to a variable.

If the next number is equal to the vResc variable then the vSite variable is used as the site for that row.

Number	Lot	Site
1	abc123	London
1	abc456	London
1		London
2	acb213	Paris
3	cba321	Madrid
3		Madrid
4		Rome

Hope this makes sense - Subroutines might not be the right way to code this but not overly experienced in QV.

Code below:

Let vResc = '';
Let vSite = '';

Sub AssignSite1

Let vResc = Number;

SiteMethod:
LOAD if(not ISNULL(Lot),ApplyMap('PSMAP', Lot, 'Lot Without Map')) as MfgSite
;
Let vSite = MfgSite;

ENDSUB;

SUB AssignSite2

Let vResc = Number;

if(isNull(Lot), ApplyMap('MaskMapOne', Number, 'Multi-Site')) as MfgSite;

Let vSite = MfgSite;

ENDSUB;

Map_Applied:
Load
Number,
Lot,
Description,

      if(Number = vResc, vSite,
      if(Number <> vResc, CALL AssignSite1, CALL AssignSite2 )) as MfgSite

       Resident Stock_Data;

Drop Table Stock_Data;

Thanks,

Tom

Re: Calling Subs within Loads to return value

annafuksa1 — Mon, 24 Apr 2017 10:43:24 GMT

COuld you post your app ?

and try

Map_Applied:

Load

Number,

Lot,

Description,

if(Number = vResc, vSite,

if(Number <> vResc, if(not ISNULL(Lot),ApplyMap('PSMAP', Lot, 'Lot Without Map'))

,if (isNull(Lot), ApplyMap('MaskMapOne', Number, 'Multi-Site')) )) as MfgSite

Resident Stock_Data;

Drop Table Stock_Data;

you are not suppose to use subroutine inside the expression.

you can also use function pick instead.

Re: Calling Subs within Loads to return value

Peter_Cammaert — Mon, 24 Apr 2017 10:47:05 GMT

You cannot CALL SubRoutines from inside a LOAD statement, you can only use functions (just like any QlikView function). SubRoutines don't return values, only functions do.

Redefine your code as a set of macro functions that return values and use them - without the CALL keyword - in a LOAD statement.

BTW CALL is a statement keyword. You cannot embed other statements in a LOAD...

Re: Calling Subs within Loads to return value

tom2qlik — Mon, 24 Apr 2017 11:24:43 GMT

Thank you for your response.

I can post the app but there is little more detail on it than what I've posted and I can't post the full app because of sensitive data.

I have thought of your solution however How will the variable be assigned a new value?

The next number in the source table will always be different so the second condition in the if statement will always be met which is not the required outcome.

I need the variable vResc to change to with every new number for this method to work.

Re: Calling Subs within Loads to return value

annafuksa1 — Mon, 24 Apr 2017 12:38:09 GMT

I want to see the data and be able to test my solution

I just look at this and make a changes to code but was not able to test it so may have some little problems -it was just to give you idea.

for variable you can choose function Peek or maybe you may need to create function for this case

its a little difficult to say now

but it can be easier if you will post as well expecting result with current app

Re: Calling Subs within Loads to return value

tom2qlik — Tue, 25 Apr 2017 08:12:49 GMT

I think this is probably the closest answer I am going to get as to if this solution will work. Thanks for clarifying that CALLs can't be used within Loads.

I have not used macros in Qlikview before so thanks for bringing them to my attention.

Re: Calling Subs within Loads to return value

Peter_Cammaert — Tue, 25 Apr 2017 08:49:05 GMT

To help you get started, see this example: Using Macro functions in Script

The non-standard function NextContractDate() is actually a user-defined macro function. See QV Desktop->Tools->Edit Module...

Good luck,

Peter