Two identical while loads

[#1]

I'm fetching the exact same type of data from another source.

Hence i added an identical load (concatenate) underneath the one you helped me out with, however this ruined the date intervals.

Do you know why?

Any suggestions how to arrange the second load?

Thanks in advance

//O

//FIRST LOAD

TRANSACTIONS:

LOAD [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

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

RIGHT JOIN
LOAD date(Date) as DATE
FROM

(ooxml, embedded labels, table is Sheet1);

//SECOND LOAD

CONCATENATE
LOAD [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

(ooxml, embedded labels, header is 1 lines, table is [HISTORICAL DATA])
While [FLOW EFFECTIVE DATE] + IterNo() -1 <= [FLOW MATURITY DATE];

[jonathandienst · Accepted solution]

Hi

What purpose is the DATE load? Should this be performed after the concatenate?

If that is not the problem, its possible that the concatenation and While/IterNo construct are conflicting in some way. To solve that, perform the second load into a temp table and the concatenate the completed resident table (and then drop it). Like this:

TRANSACTIONS:

LOAD [INSERTION DATE],

...

FROM ....

...

T_TRANS:

LOAD [INSERTION DATE],

      TIME,

      TRADER,

        [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

(ooxml, embedded labels, header is 1 lines, table is [HISTORICAL DATA])

While [FLOW EFFECTIVE DATE] + IterNo() -1 <= [FLOW MATURITY DATE];

Concatenate (TRANSACTIONS)

LOAD * Resident T_TRANS;

DROP TABLE T_TRANS;

HTH

Jonathan