Load data from variables created in a SUB

[Anonymous]

Hi there

I'm trying to do a load of data from a table with 3 fields; date, description and amount.

I need to parse the description  and create 4 new fields from the description field and then load those 3 table fields plus the 4 new calculated fields.

I'm trying to use a SUB to achieve this but I'm just a Qlik newbie and I must be missing something. It looks like the SUB is running but the Description and Amount values are not being passed in

Any help would be appreciated.

Neil

[petter]

The SUB will not be able to be called like a function from your last load statement.

Actually you can do away with the SUB entirely by refactoring your logic. All logic can be maintained in the last load statement by using function If() similar to the If() in line 73 in your code. All fields have to be treated separately and nesting of ifs have to be done. I made a quick (not debugged or tested) refactoring that should be pretty close to what will work for you:

[Bank Statements]:
LOAD
  'AlePhoto' AS [Bank Account],
  If([Amount]<0, [Amount] * -1 , [Amount]) AS [Amount],

  If( Upper([Description]) LIKE 'TRF*FROM*GABIN*' ,
    'Personal T-In' ,
    If( Upper([Description]) LIKE 'TRF*TO*GABIN*' ,
      'Personal TR-Out' ,
      If( [Amount] > 0 ,
        'Income' ,
        'Expense' 
     )
    )
  ) AS [Transaction Type],

  If( Upper([Description]) LIKE '*PAYPAL*' ,
    'PayPal',
    If( Upper([Description]) LIKE 'TRF TO *0##### ######*' OR Upper([Description]) LIKE 'TRF*TO-*' ,
      SubField([Description],' ',3)
    )
  ) AS [Institution],

  If( Upper([Description]) LIKE '*PAYPAL*' ,
    Left(Trim(SubField([Description],':',3)),6) ,
    If( Upper([Description]) LIKE 'TRF*FROM-*' OR Upper([Description]) LIKE 'TRF*TO-*' ,
      Replace(SubField([Description],'-',2),'Ref-',''),
      If( Upper([Description]) LIKE 'TRF TO *0##### ######*' OR Upper([Description]) LIKE 'TRF*TO-*' ,
        SubField([Description],' ',4)
      )
    )
  ) AS [RelatedAccount],   

  If( Upper([Description]) LIKE '*PAYPAL*' ,
    SubField([Description],':',2),
    If( Upper([Description]) LIKE 'TRF*FROM-*' OR Upper([Description]) LIKE 'TRF*TO-*' ,
      Trim(SubField([Description],'-',3))
  ) AS [Reference]
RESIDENT [3090076S82];

Then you can delete all lines from line 31 to 67 in your original code and use what I suggested as a replacement of the lines starting at line 68 and to the end of your code. The code I suggest might of course contain some logical errors and typos that you have to test yourself with your own data.

[Anonymous]

thanks for your reply Petter and yes creating the series of inline IF/THEN statements did work, but I'd still like to explore if it possible to use a SUB like function to calculate and return the new values to the load script.

Are you saying we definitely cannot use SUB's as functions in data load scripts to return values as in my illustration above?

Neil

[petter · Accepted solution]

It would be rather cumbersome. It is because a SUB cannot be called for each row that is being processed/read by a LOAD statement. Why do you have a preference for creating a SUB instead of doing this in a functional style? Is it because you like to chunk up the code to make it more readable?

By using a combination of Pick() and WildMatch() the code looks a little bit different and maybe more readable and easier to maintain (it is perhaps a matter of taste). It would look something like this:

[Bank Statements]:  
LOAD  
  'AlePhoto' AS [Bank Account],  
  If([Amount]<0, [Amount] * -1 , [Amount]) AS [Amount],  

  Pick( WildMatch( DESC , 'TRF*FROM*GABIN*' , 'TRF*TO*GABIN*' ) + 1 ,     // the + 1 is there to add a non-match option
     If( [Amount] > 0 , 'Income' , 'Expense' )  // For non-match
    ,'Personal T-In'   
    ,'Personal TR-Out'  
  ) AS [Transaction Type], 

  Pick( WildMatch( DESC , '*PAYPAL*' ,  'TRF TO *0##### ######*', 'TRF*TO-*' ) + 1,    
    Null()  // For non-match
    , 'PayPal'  
    ,SubField(DESC,' ',3)  
    ,SubField(DESC,' ',3)  
  ) AS [Institution],  

  Pick( WildMatch( DESC , '*PAYPAL*' , 'TRF*FROM-*' , 'TRF*TO-*' ) + 1,
    Null() 
    , Left(Trim(SubField(DESC,':',3)),6) 
    , Left(Trim(SubField(DESC,':',3)),6)
    , Replace(SubField(DESC,'-',2),'Ref-','')
    , SubField(DESC,' ',4)  
  ) AS [RelatedAccount],     

  Pick( WildMatch( DESC , '*PAYPAL*' , 'TRF*FROM-*' , 'TRF*TO-*' ) + 1 ,
    Null()
    , SubField(DESC,':',2) 
    , Trim(SubField(DESC,'-',3))
    , Trim(SubField(DESC,'-',3))
  ) AS [Reference]
;

LOAD
  Upper( [Description] ) AS DESC,
  *  
RESIDENT [3090076S82];  

[petter]

Here is a variant that do use the SUB although it will use function-logic in the LOAD statement. With this you get the code style and almost the same syntax as you prefer. I employ string variables and $-sign expansion to insert them into the final LOAD statement:

LET vDescription = '[Description]';
LET vAmount = '[Amount]';

SUB DecodeDescriptionLogic( vDescription , vAmount , vTransactionType , vInstitution , vRelatedAmount , vReference )

  SET vTransactionType = '
    Pick( WildMatch( Upper($(vDescription)) , ''TRF*FROM*GABIN*'' , ''TRF*TO*GABIN*'' ) + 1 ,
       If( $(vAmount) > 0 , ''Income'' , ''Expense'' )  
      ,''Personal T-In''   
      ,''Personal TR-Out''  
    )
  ';

  SET vInstitution = '
    Pick( WildMatch( Upper($(vDescription)) , ''*PAYPAL*'' ,  ''TRF TO *0##### ######*'', ''TRF*TO-*'' ) + 1,    
      Null()  // For nno-match
      , ''PayPal''  
      ,SubField($(vDescription) ,'' '',3)  
      ,SubField($(vDescription) ,'' '',3)  
    )
  ';

  SET vRelatedAccount = '
    Pick( WildMatch( Upper($(vDescription))  , ''*PAYPAL*'' , ''TRF*FROM-*'' , ''TRF*TO-*'' ) + 1,
      Null() 
      , Left(Trim(SubField($(vDescription) ,'':'',3)),6) 
      , Left(Trim(SubField($(vDescription) ,'':'',3)),6)
      , Replace(SubField($(vDescription) ,''-'',2),''Ref-'','''')
      , SubField($(vDescription),'' '',4)  
    )  
  ';     

  SET vReference = '
    Pick( WildMatch( Upper($(vDescription))  , ''*PAYPAL*'' , ''TRF*FROM-*'' , ''TRF*TO-*'' ) + 1 ,
      Null()
      , SubField($(vDescription) ,'':'',2) 
      , Trim(SubField($(vDescription) ,''-'',3))
      , Trim(SubField($(vDescription) ,''-'',3))
    )
  ';

END SUB

SUB DecodeDescriptionCLEANUP
  // Delete text variables
  vTransactionType=;
  vInstitution=;
  vRelatedAccount=;
  vReference=;
END SUB

Call DecodeDescriptionLogic(vDescription , vAmount , vTransactionType , vInstitution , vRelatedAmount , vReference )

[Bank Statements]:  
LOAD  
  'AlePhoto' AS [Bank Account],  
  If([Amount]<0, [Amount] * -1 , [Amount]) AS [Amount],  
  $(vTransactionType) AS [Transaction Type], 
  $(vInstitution) AS [Institution],  
  $(vRelatedAccount) AS [RelatedAccount],
  $(vReference) AS [Reference]
RESIDENT [3090076S82];  

Call DecodeDescriptionCLEANUP

vDescription=;
vAmount=;

[Anonymous]

that is brilliant Petter. thank you.

I think I'll go with your second suggestion (the inline PICK/WILDMATCH)  for now and might use the SUB variant you suggested if I ever run into a road block.

Thanks again for your detailed response. Really appreciate your help.

Neil