Creating new field in script based on other fields grouped by order number

[hopkinsc]

Hi all,

i can't seem to get this formula correct, what i want is to create a new field based on some date fields.

so the logic would be..

If there are only [order item cancel date]s at the line item level then [case fill date] = MAX([order item cancel date])

If there are only [invoice date]s at the line item level then [case fill date] = MAX([invoice date])

If there are a mix of [order item cancel date]s and [invoice date]s the [case fill date] = MAX([invoice date])

If there are neither [order item cancel date]s or [invoice date]s then [case fill date] = NULL

can anyone point me in the right direction please?

[hopkinsc]

I have just updated the sample files with what i have working so far.

as you can see from the selections, i have one order number selected and it has 2 different Invoice Dates. i can get the new field to show these Invoice dates but i want the new field to only show the max invoice date for the whole order. so for the order selected i would want 27/10/2015 to be against all line items.

[sasiparupudi1]

MaxDates:

Load

Lineno

Max (canceldate) as canceldate

Max (invoicedate)as invoivedate

Resident LineTable

Group by lineno;

Map_cancelDate:

Mapping load

Lineno,

Canceldate

Resident MaxDates;

Map_InvoiceDate:

Mapping load

Lineno,

Invoicedate

Resident MaxDates;

FinalTable:

Load

Lineno,

If yourflag conditions

;

Load

Lineno,

Applymap ('Map_InvoiceDate',lineno,Null ())  as maxinvoicedate,

Applymap ('Map_CancelDate',lineno,Null ())  as maxCanceldate,

Casefilldate

Resident linetable;

[hopkinsc]

Hi, thanks for your reply, but unfortunately i cannot get this working..

[Anonymous]

Hi Chris!

try this...

load *,

    if(not isnull([Order Item Cancel Date]) and not isnull([Invoice Date]) or isnull([Invoice Date]) and isnull([Order Item Cancel Date]),  date(max([Invoice Date])),

if(not isnull([Order Item Cancel Date]) and isnull([Invoice Date]), date(max([Order Item Cancel Date])),

if(isnull([Order Item Cancel Date]) and not isnull([Invoice Date]), date(max([Invoice Date])),

       null()))) as newfield

[hopkinsc]

I can't get this working either.

[sunny_talwar]

I may be wrong, but seems like you just need this

DataTMP:

LOAD [Order Number],

    [Line Number],

    [Line Type],

    [Invoice Date],

    [Order Item Cancel Date],

    [Quantity Ordered - 9L],

    Alt([Invoice Date] ,[Order Item Cancel Date]) as [Case Fill Date]

FROM Sample.xls

(biff, embedded labels, table is Sheet1$);

[hopkinsc]

Hi Sunny,

that would work for most of the orders, but for example, if i select the order number 15001624, there are 2 different invoice dates, so i would need the max invoice date  applied to them all and it would be the same if there were multiple cancel dates per order - i would need the max cancel date against all.

does that make sense?

[sunny_talwar · Accepted solution]

May be this

DataTMP:

LOAD [Order Number],

    [Line Number],

    [Line Type],

    [Invoice Date],

    [Order Item Cancel Date],

    [Quantity Ordered - 9L]

FROM Sample.xls

(biff, embedded labels, table is Sheet1$);

Left Join (DataTMP)

LOAD [Order Number],

Date(Max(Alt([Invoice Date] ,[Order Item Cancel Date]))) as [Case Fill Date]

Resident DataTMP

Group By [Order Number];

[hopkinsc]

perfect! thanks Sunny!