3 Replies Latest reply: Jan 27, 2017 6:15 AM by Marcus Sommer RSS

    Script in load script

    s Walter

      Hi,

       

      i want to load a table with 4 fields for example. Then i want to add 1 field in the load script. The value for this field can i get by a script with variables, if else etc.

       

      Example:

       

      tbl_urls:     
      LOAD
           Product,
           Customer,
           Sales,
           URLs,
      
           Here start a script like 
           let vCleanURL = Replace(URLs,'https://','');
           let vCleanURL = Replace($(vCleanURL),'http://','');
           ...more script... as cleanURL
      
      
      FROM [lib://source/urls.xlsx]
      (ooxml, embedded labels, table is Tabelle1);
      

       

      Is something like this possible?

       

      Regards,

      sam

        • Re: Script in load script
          Jonathan Dienst

          Add the derived field using something like

           

          bl_urls:      

          LOAD 

              Product, 

              Customer, 

              Sales, 

              URLs, 

              Replace(Replace(URLs,'https://',''), 'http://','') as CleanURL 

            ...

           

          You can nest the Replace() statements further if you want to do further substitution.

            • Re: Script in load script
              s Walter

              Thanks, good to know.

               

              If i need a longer script to edit values for a new field, is this possible to do that in a load script?

               

              Or can i write an own function like myFunction(){...code...return variable} and call it in the load script?

               

              tbl_urls:     
              LOAD 
                  Product, 
                  Customer, 
                  Sales, 
                  URLs, 
                
                  ...longer script (or call myFunction())... as cleanURL 
              
              FROM [lib://source/urls.xlsx] 
              (ooxml, embedded labels, table is Tabelle1); 
              

               

              The script could do a lot of things like use variables, if statements etc. It doesn´t matter for my question what, because i would like to know, is this possible and how can i do it?

               

              regards,

              sam

                • Re: Script in load script
                  Marcus Sommer

                  You could use a variable with parameter which worked like an own function, for example:

                   

                  set myFunction = Replace(Replace($1,'https://',''), 'http://','');

                   

                  tbl_urls:      

                  LOAD  

                      Product,  

                      Customer,  

                      Sales,  

                      URLs,  

                      $(myFunction(URLs)) as cleanURL  

                    FROM [lib://source/urls.xlsx]  

                  (ooxml, embedded labels, table is Tabelle1);

                   

                  - Marcus