Calling Subs within Loads to return value

[tom2qlik]

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

[annafuksa1]

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.

[Peter_Cammaert · Accepted solution]

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...

[tom2qlik]

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.

[annafuksa1]

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

[tom2qlik]

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.

[Peter_Cammaert]

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