Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
hopkinsc
Partner
Partner

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

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?

1 Solution

Accepted Solutions
sunny_talwar

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

Capture.PNG

View solution in original post

9 Replies
hopkinsc
Partner
Partner
Author

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
Master III
Master III

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
Partner
Partner
Author

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

hdbucher
Contributor III
Contributor III

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
Partner
Partner
Author

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
Partner
Partner
Author

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.

Capture.JPG

does that make sense?

sunny_talwar

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

Capture.PNG

View solution in original post

hopkinsc
Partner
Partner
Author

perfect! thanks Sunny!