Script in load script

[Anonymous]

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

[jonathandienst]

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.

[Anonymous]

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

[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