Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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];
Try with a Left Join and let me know
When trying left join, same result occurs, from what I can see..
There is a very simple way:
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,
'I' AS EOD_ADJ
Thanks Alessandro
'I' AS EOD_ADJ is loaded from another sheet and it is a single cell, not a table.
So Im not sure how you mean
You don't get any synth keys without the last join?
Could you post a screenshot of you data model and your script or document log?
As I can see from where condition, it's a single value so you can store it in variable and then add with table.
But the value for I is Always the same, isn't it?
It is a unique value, a constant?
No It changes every day unfortunately
Ok but when you try to add the new column to the table the value is only one?