topic Re: Min function - 1 to many relationship in QlikView

Min function - 1 to many relationship

Thu, 24 Oct 2013 15:13:09 GMT

In the following code I have a one to many relationship. One [Sales Order] for multiple [Bill To] dates.

I need to find the lowest Bill to date for each sales order.

Sales Order Bill To

4000003 11/01/2008

4000005 11/01/2009

4000025 11/01/2010

4000085 11/01/2011

4000078 11/01/2012

4000458 11/01/2013

I'm using the following code, but im still receiving multiple Bill To dates for 1 Sales Order.

LEFT KEEP (Main)

LOAD

DOC_NUMBER,

min(date(invoice_date)) as inv_date_sd

FROM source

GROUP BY DOC_NUMBER, invoice_date;

Does anyone have any suggestions?

Thanks

Re: Min function - 1 to many relationship

Jason_Michaelides — Thu, 24 Oct 2013 15:25:48 GMT

Is invoice_date the same as "Bill To" date? If so, remove it from the Group By clause.

Hope this helps,

Jason

Re: Min function - 1 to many relationship

Clever_Anjos — Thu, 24 Oct 2013 15:36:24 GMT

LEFT KEEP (Main)
LOAD
DOC_NUMBER,
min(date(invoice_date)) as inv_date_sd
FROM source
GROUP BY DOC_NUMBER;

Re: Min function - 1 to many relationship

Thu, 24 Oct 2013 16:31:42 GMT

That worked, I removed the invoice_date from the Group By clause and it worked correctly.

Why did this resolve the issue?

Thank you both for your help.

Re: Min function - 1 to many relationship

Jason_Michaelides — Thu, 24 Oct 2013 16:35:22 GMT

Grouping by all fields is the same as not grouping. You want to group the rows by DOC_NUMBER, and from within each group get the minimum invoice date.