Apply one specific value to all rows

[#1]

I need to add a single value for each and every row (EOD_ADJ). But when I try to join all I get is a bunch synthetic keys.

Any ideas how to do it?

LOAD Distinct
      [INSERTION DATE],
     TIME,
     [FLOW TYPE],
     AMOUNT,
     date([FLOW EFFECTIVE DATE]) as [FLOW EFFECTIVE DATE],
     date([FLOW MATURITY DATE]) as [FLOW MATURITY DATE],
     Date([FLOW EFFECTIVE DATE] + IterNo() -1 ) as DATE,
     TEXT
FROM
[Log 2.1.xlsm]
(ooxml, embedded labels, table is TRANSACTIONS)
While [FLOW EFFECTIVE DATE] + IterNo() -1 <= [FLOW MATURITY DATE];


Right join LOAD
     I AS EOD_ADJ
FROM
[Log 2.1.xlsm]
(ooxml, no labels, table is [Holidays Etc])
WHERE RecNo() > 1 AND RecNo() < 3; 

[alexandros17 · Accepted solution]

TMP:

Right join LOAD
     I AS EOD_ADJ
FROM
[Log 2.1.xlsm]
(ooxml, no labels, table is [Holidays Etc])
WHERE RecNo() > 1 AND RecNo() < 3; 

Let myVar = Peek('EOD_ADJ', 0, 'TMP');

LOAD Distinct
      [INSERTION DATE],
     TIME,
     [FLOW TYPE],
     AMOUNT,
     date([FLOW EFFECTIVE DATE]) as [FLOW EFFECTIVE DATE],
     date([FLOW MATURITY DATE]) as [FLOW MATURITY DATE],
     Date([FLOW EFFECTIVE DATE] + IterNo() -1 ) as DATE,
     TEXT,

     $(myVar) as EOD_ADJ

FROM
[Log 2.1.xlsm]
(ooxml, embedded labels, table is TRANSACTIONS)
While [FLOW EFFECTIVE DATE] + IterNo() -1 <= [FLOW MATURITY DATE];

[#3]

I like your Idea!

Tried the below but the value of EOD_ADJ will not be stored in the variable when the table is dropped.

What am I doing wrong here?

LOAD Distinct
[INSERTION DATE],
     TIME,
     [FLOW TYPE],
     AMOUNT,
     date([FLOW EFFECTIVE DATE]) as [FLOW EFFECTIVE DATE],
     date([FLOW MATURITY DATE]) as [FLOW MATURITY DATE],
     Date([FLOW EFFECTIVE DATE] + IterNo() -1 ) as DATE,
     TEXT
FROM
Log 2.1.xlsm]
(ooxml, embedded labels, table is TRANSACTIONS)
While [FLOW EFFECTIVE DATE] + IterNo() -1 <= [FLOW MATURITY DATE];

EOD_ADJUSTMENT:
LOAD
     I AS EOD_ADJ
FROM
[Log 2.1.xlsm]
(ooxml, no labels, table is [Holidays Etc])
WHERE RecNo() > 1 AND RecNo() < 3;

Set vEODADJ = EOD_ADJ;

Drop table EOD_ADJUSTMENT;

[senpradip007]

Try this. Hope it will help.

T2:

I AS EOD_ADJ
FROM
[Log 2.1.xlsm]
(ooxml, no labels, table is [Holidays Etc])

T1:

LOAD Distinct
      [INSERTION DATE],
     TIME,
     [FLOW TYPE],
     AMOUNT,
     date([FLOW EFFECTIVE DATE]) as [FLOW EFFECTIVE DATE],
     date([FLOW MATURITY DATE]) as [FLOW MATURITY DATE],
     Date([FLOW EFFECTIVE DATE] + IterNo() -1 ) as DATE,
     TEXT,

     peek(EOD_ADJ, 0, 'T2') as EOD_ADJ
FROM
[Log 2.1.xlsm]
(ooxml, embedded labels, table is TRANSACTIONS)
While [FLOW EFFECTIVE DATE] + IterNo() -1 <= [FLOW MATURITY DATE];

Drop table T2;