Cannot process fields on Outer Join?

[#1]

In my script I have an OrderHeader table with the relevant fields

KEY, OrderNo, InvoiceDate, ProcessDate - we have a batch processing system so the process date can be ahead of the invoice date.

KEY is a combination of the ProcessDate and OrderNo

We have transactions that are hand entered that can adjust sales/margin that go into a different table and do not have a valid Key.  As well the adjustment does not go back against the original process/invoice date so I cannot make a matching Key.  Also in the adjustment table the invoice date does not carry the Day.

To fix this in the adjustments table, I create a "dummy" key consisting of a dummy number and the OrderNo.

For Invoice Date I force all adjustments to the end of month date by using the MonthEnd function.

I Outer Join this with the OrderHeader file.    In the OrderHeader, I can see all the combined rows properly when I dump to Excel.

The next step is to derive the Fiscal Month from the Invoice Date on the Order Header.

The problem  I am having is that the script is creating Fiscal Dates for the true OrderHeader dates but no the OuterJoin fields.

If the Outer Join properly merges the Row, why does it not calculate the fiscal month from the InvoiceDate properly

// Create Link Dates for Order Header to Calendar

DateTemp:

LOAD

    KEY,

    Date_ProcDate,

    Date_InvoiceDate

Resident OrderHeader;

Left Join (OrderHeader)

LOAD

    KEY,

    date(floor(Date_InvoiceDate)) AS Link_Date,

    date(floor(Date_InvoiceDate)) AS Date_InvoiceDate        //Load it a second time for making date range selections

Resident DateTemp;

Drop Table DateTemp;

// -------------------------------------------------------------------------------------

TEMP:

LOAD

    num(min(Date_InvoiceDate)) AS MinDate,

    num(max(Date_InvoiceDate)) AS MaxDate,

    num(max(Date_ProcDate)) AS ProcessDate

RESIDENT

    OrderHeader;

LET vMinDate = peek('MinDate', 0, 'TEMP');

LET vMaxDate = peek('MaxDate', 0, 'TEMP');

LET vProcessDate = peek('ProcessDate', 0, 'TEMP');

DROP TABLE TEMP;

DateIsland:

LOAD

    date($(vMinDate) + rowno() - 1) AS D,

    year($(vMinDate) + rowno() - 1) AS Y,

    month($(vMinDate) + rowno() - 1) AS M,

    date(monthstart($(vMinDate) + rowno() - 1), 'MMM-YYYY') AS MY

AUTOGENERATE

    vMaxDate - vMinDate + 1;

LET vInvDate =$(vMaxDate);

LET vToday = $(vProcessDate);

LET vDate_Today=Date($(vToday));

LET vSelectedDay = $(vProcessDate);

LET vDate_Selected=Date($(vSelectedDay));

// FISCAL Month/Year

LET vCurFisMo=num(month(AddMonths(MonthsName(1,$(vInvDate),0,11),-10)),'#0');

LET vCurFisYr=num(Year(AddMonths(MonthsName(1,$(vInvDate),0,11),+2)),'#0');

LET vLastFisMo=$(vCurFisMo)-1;

LET  vLastFisYr=$(vCurFisYr)-1;

//num(month($(vInvDate)));

LET vThisYear = year($(vInvDate));

LET vLastYear = year($(vInvDate)) - 1;

LET vThisMonthText = month($(vInvDate));

LET vLastMonth = num(month(addmonths($(vInvDate), -1)));

LET vLastMonthText = month(addmonths($(vInvDate), -1));

LET vTodayWithinYear = num($(vInvDate) - yearstart($(vInvDate))) + 1;

LET vTodayWithinMonth = num($(vInvDate) - monthstart($(vInvDate))) + 1;

//LET vPriorYearDate = '=date(addyears(max(Date_ProcDate),-1),' & chr(39) & 'M/DD/YYYY' & chr(39) & ')';

LET vPriorYearDate = AddYears($(vProcessDate),-1);

Calendar:

LOAD

    D AS Link_Date,

    D AS Date,

//    Basic Date Dimensions

    Y AS Year,

    M AS Month,

    MY AS MonthYear,

    num(MY) AS NumericMonthYear,

    'Q' & ceil(M / 3) AS Quarter,

    week(D) AS Week,

    weekday(D) AS Weekday,

    day(D) AS Day,

    date(D, 'MM/DD') AS DateMMDD,

//Fiscal Dates

     'Q' & Ceil(month(AddMonths(MonthsName(3,D,0,11),-10))/3)     as FisQuarter,

     num(month(AddMonths(MonthsName(1,D,0,11),-10)),'#0')     as FisMo,

    num(Year(AddMonths(MonthsName(1,D,0,11),+2)),'#0')         as FisYr,

    month(D)                                                             as FisMoShort,

[jonathandienst]

I am having difficulty connecting your narrative to your script.

Where do you load the invoices?

Where do you load the adjustments?

What purpose does the DateIsland serve and is it relevant to your problem?

What do you do with all those variables and are they relevant to your problem?

Which fiscal field is not properly linked to which invoice/adjustment field?

And your script seems to be cut short. Where is the FROM clause for Calendar?

[#3]

Yeah - I knew it would be a mouthful and was late/tired.

I went to strip down my script to post it and noticed that I forgot to mention that the Fiscal Dates for the sales adjustments were pulling properly for the current Fiscal year.

So I looked at my date format for the current FY and changed all my prior FY Sales Adjustments (each FY is a different QVD source) in my script to the same "formula" and then the lines in my calendar script process properly and assign a FisYr and FisMo.

num(month(AddMonths(MonthsName(1,D,0,11),-10)),'#0')     as FisMo,

num(Year(AddMonths(MonthsName(1,D,0,11),+2)),'#0')         as FisYr,

[vincent_ardiet]

Hi,

Agree with Jonathan, your script doesn't reflect your speech and the last Load is not ended (no Resident or From).

You say that you have use MonthEnd for the adjustments and it seems that your issue concerns those lines. A typical mistakes with MonthEnd is the fact that the hour changes also, so it's end of month and end of the day. So, when you try to join it can produce some jokes. You can use DayStart(MonthEnd(...)).

Regards,

Vincent