How to link those tables?

[#1]

Hello Everyone,

I have yearly goal data and actual data. I need compare actual to gaol by each month.

I need your help on link those data together.

Please see attached sheet for example.

Thank you very much in advance.

Dust

[Nicole-Smith · Accepted solution]

This should do the trick (I had to do some clean up on the goal tables because your data isn't very clean):

Actual:

LOAD Division,

    Proj,

    Revenue,

    Date

FROM

(ooxml, embedded labels, table is Actual);

for i=2013 to year(today())

    GoalTemp:

    CrossTable(Month,Goal,2)

    LOAD *;

    LOAD Goal as Division,

        $(i) as Year,

        [1.000000] as [1],

        [2.000000] as [2],

        [3.000000] as [3],

        [4.000000] as [4],

        [5.000000] as [5],

        [6.000000] as [6],

        [7.000000] as [7],

        [8.000000] as [8],

        [9.000000] as [9],

        [10.000000] as [10],

        [11.000000] as [11],

        [12.000000] as [12]

    FROM

    (ooxml, embedded labels, table is [$(i)Goal], filters(

    Remove(Row, Pos(Top, 1)),

    Remove(Row, RowCnd(CellValue, 1, StrCnd(null)))

    ));

next i

Goal:

LOAD Division,MakeDate(Year,num(Month),1) as StartDate,MonthEnd(MakeDate(Year,num(Month),1)) as EndDate,Goal;

LOAD Division,Year,num(trim(Month)) as Month,Goal

RESIDENT GoalTemp;

DROP TABLE GoalTemp;

inner join

IntervalMatch(Date,Division)

load StartDate, EndDate, Division

resident Goal;

Example file is also attached.

[#3]

Nicole -- Thank you very much.

You solution is super good.

Wish you all the best and thank you again.

Dust

[#4]

Nicole - Thank you very much for your help.

I have same structure profit need to compare. Can you help to write script to load them together?

I appreciate your help.

Dust