Data load editor - condition to load a line from another file

[abonnery]

Hello, 

I am trying to create a condition in my data editor script to load one specific value from another file if a condition is met. 

Here is the context: 

I have 2 excel files which contains the data I am interested in loading:  "Data Sources versioning" and "File2".

In the data load editor I am uploading the data from the file "Data Sources versioning" with the following script (it works perfectly):

LOAD
"Source Name",
Version,
"last upload",
"Detail"
FROM [lib://SMS/Data Sources versioning.xlsx]
(ooxml, embedded labels, header is 1 lines, table is [Feuil1-1]); 

I would like to add to this script a condition that would do the following:

if "Source Name"="document A" then "last upload"="date of extraction" from the tab 1 of "File2". 

In the tab 2 of the file2 there is only one value under the "date of extraction" field. The File2 changes daily contrary to the "data source versioning" file hence why I would like to create that condition. 

Is this possible ? If so, what would be the script to realize this action? 

Thank you very much for your help.

[Quy_Nguyen]

Hi, you could get the date of extraction from file 2, store it in a variable and use in the load script condition. Try this:

// Load the date of extraction from File 2
Version:
LOAD
    "date of extraction" as update_time
FROM [lib://SMS/File2.xlsx]
(ooxml, embedded labels, table is Sheet1)
Where Len("date of extraction") > 0;

// Store the value to a variabe
Let vUpdateTime = Peek('update_time',0,'Version');

Data:
LOAD
  "Source Name",
  Version,
  // Use the variable for conditioning
  If("Source Name" = 'document A', $(vUpdateTime), "last upload") as  "last upload",
  "Detail"
FROM [lib://SMS/Data Sources versioning.xlsx]
(ooxml, embedded labels, header is 1 lines, table is [Feuil1-1]);