Variable in Load Editor

[MIKIEMILLER]

I have two tables and I want to have the information being brought on "Table A" be based on a cell in "Table B".  

"Table B"  is one row of data that is manually updated from time to time.  One column is named "Last Year Period" and the value as of right now is 1807.  Like I said this value changes, it is not a constant. 

"Table A" has hudreds of thousands of rows of data but one column is named FYOP and has numbers ranging anywhere from 1701 to 1908.  This data is ever growing and this FYOP field continually increases.  

I would like to either:

• put a variable in "Table B" and reference that variable in "Table A" to only bring in FYOP's that are higher than "Last Year Period"'s value.
• Reference "Table B" in a where clause in "Table A" that shows all FYOPs higher than "Last Year Period"

Is either of these options accomplishable?

~M

[jonathandienst · Accepted solution]

Code like this:

LYP:
Load [Last Year Period] 
From TableB.xlsx (ooxml, ...);

Let vFYOP = Peek('Last Year Period');
DROP Table LYP;

TableA:
Load 
	...
From TableA.xlsx (ooxml, ...)
Where FYOP > $(vFYOP);   // assume FYOP is numeric

If FYOP or the Last Year Period are text, then you will need to quote the variable in the Where clause:

Where FYOP > '$(vFYOP)';

[MIKIEMILLER]

Thank you so much.  That works perfectly.