LAE

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ThomasT

Hi Guys.

Here is a problem that I bump into often.
I have some products that I would like to have one record per month and per Sell-to Customer No_ from this table, could anyone help?

Product cost Sell-to Customer No_ ToDate FromDate
Broadband Surf40 45003089 01-04-2015 01-11-2013
Broadband Surf40 45003774 01-12-2014 01-12-2014
Broadband Surf40 45011793 01-04-2015 01-10-2014
Broadband Surf40 45016688 01-01-2015 01-01-2015
Broadband Surf40 45017233 01-04-2015 01-05-2014
Broadband Surf40 45019838 01-04-2015 01-10-2014
Broadband Surf40 45022384 01-04-2015 01-03-2014
Broadband Surf40 45022886 01-06-2014 01-11-2013
Broadband Surf40 45026663 01-04-2015 01-11-2013
Broadband Surf40 45032596 01-11-2013 01-11-2013
Broadband Surf40 45033299 01-04-2015 01-05-2014
Broadband Surf40 45034062 01-03-2014 01-02-2014
Broadband Surf40 45036913 01-05-2014 01-05-2014
Broadband Surf40 45037486 01-01-2015 01-11-2013
Broadband Surf40 45040195 01-04-2015 01-02-2015
Broadband Surf40 45040229 01-08-2014 01-11-2013
Broadband Surf80 45042906 01-07-2014 01-11-2013
Broadband Surf80 45043826 01-12-2014 01-11-2013
Broadband Surf80 45044351 01-11-2014 01-11-2013
Broadband Surf80 45045851 01-09-2014 01-11-2013
Broadband Surf80 45046241 01-04-2015 01-11-2013

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ryeh

I am assuming that the To/From Dates will always be in the same month? For the sake of this example, I only took a look at the month of the ToDate. An AggEx or Remove Duplicate node will both work. In your sample above, there were unique customer numbers, so nothing was removed. I fabricated some duplicates to show the Agg Ex and Remove Duplicate nodes in action.

You can also use the Duplicate Detection node to check which records do fall into the same grouping as others.
Attachments:
OnePerGroup.brg

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ThomasT

Sorry, I think I explained it wrong, for every combination of Sell-to Customer No_ and product cost I would like to expand the months between the FromDate and the ToDate .
Like this:

Product cost Sell-to Customer No_ ToDate FromDate
Broadband Surf40 45003089 01-11-2015 11-01-2013



Becomes:
Product cost Sell-to Customer No_ Month
Broadband Surf40 45003089 01-01-2013
Broadband Surf40 45003089 01-02-2013
Broadband Surf40 45003089 01-03-2013
Broadband Surf40 45003089 01-04-2013
Broadband Surf40 45003089 01-05-2013
Broadband Surf40 45003089 01-06-2013
Broadband Surf40 45003089 01-07-2013
Broadband Surf40 45003089 01-08-2013
Broadband Surf40 45003089 01-09-2013
Broadband Surf40 45003089 01-10-2013
Broadband Surf40 45003089 01-11-2013
Broadband Surf40 45003089 01-12-2013
Broadband Surf40 45003089 01-01-2014
Broadband Surf40 45003089 01-02-2014
Broadband Surf40 45003089 01-03-2014
Broadband Surf40 45003089 01-04-2014
Broadband Surf40 45003089 01-05-2014
Broadband Surf40 45003089 01-06-2014
Broadband Surf40 45003089 01-07-2014
Broadband Surf40 45003089 01-08-2014
Broadband Surf40 45003089 01-09-2014
Broadband Surf40 45003089 01-10-2014
Broadband Surf40 45003089 01-11-2014

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ryeh

Ah, ok. So still use the Agg Ex, but use groupMin and groupMax to get the To/From dates.
Attachments:
OnePerGroup1.brg

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ThomasT

Thanks Ryeh.
But as you can see in the data, I already have the to and from date(groupMax and groupMin).
I want one record per month, per customer and product between the two dates.

Product cost Sell-to Customer No_ Month
Broadband Surf40 45003089 01-01-2013
Broadband Surf40 45003089 01-02-2013
Broadband Surf40 45003089 01-03-2013
Broadband Surf40 45003089 01-04-2013
Broadband Surf40 45003089 01-05-2013
Broadband Surf40 45003089 01-06-2013
Broadband Surf40 45003089 01-07-2013
Broadband Surf40 45003089 01-08-2013
Broadband Surf40 45003089 01-09-2013
Broadband Surf40 45003089 01-10-2013
Broadband Surf40 45003089 01-11-2013
Broadband Surf40 45003089 01-12-2013
Broadband Surf40 45003089 01-01-2014
Broadband Surf40 45003089 01-02-2014
Broadband Surf40 45003089 01-03-2014
Broadband Surf40 45003089 01-04-2014
Broadband Surf40 45003089 01-05-2014
Broadband Surf40 45003089 01-06-2014
Broadband Surf40 45003089 01-07-2014
Broadband Surf40 45003089 01-08-2014
Broadband Surf40 45003089 01-09-2014
Broadband Surf40 45003089 01-10-2014
Broadband Surf40 45003089 01-11-2014

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ryeh

Ok, I get it now! One more attempt.
Attachments:
OnePerMonth.brg

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ThomasT

Just perfect!

Thank you so much!