topic Re: Creating new field in script based on other fields grouped by order number in QlikView

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

hopkinsc — Wed, 25 Nov 2020 16:16:04 GMT

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?

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

hopkinsc — Thu, 15 Mar 2018 16:51:08 GMT

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.

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

sasiparupudi1 — Thu, 15 Mar 2018 16:53:36 GMT

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;

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

hopkinsc — Thu, 15 Mar 2018 17:23:57 GMT

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

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

Anonymous — Thu, 15 Mar 2018 21:23:41 GMT

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

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

hopkinsc — Fri, 16 Mar 2018 11:20:17 GMT

I can't get this working either.

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

sunny_talwar — Fri, 16 Mar 2018 12:06:34 GMT

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$);

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

hopkinsc — Fri, 16 Mar 2018 12:22:26 GMT

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?

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

sunny_talwar — Fri, 16 Mar 2018 12:27:15 GMT

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];

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

hopkinsc — Fri, 16 Mar 2018 12:33:18 GMT

perfect! thanks Sunny!