5 Replies Latest reply: Nov 18, 2017 3:46 PM by Neil Gabin RSS

    Load data from variables created in a SUB

    Neil Gabin

      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

       

      qlik-script-help.jpg

        • Re: Load data from variables created in a SUB
          Petter Skjolden

          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.

            • Re: Load data from variables created in a SUB
              Neil Gabin

              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

                • Re: Load data from variables created in a SUB
                  Petter Skjolden

                  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];  
                  
                  • Re: Load data from variables created in a SUB
                    Petter Skjolden

                    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=;