Packing_plans - how to get them unique for past months?

[datanibbler]

Hi,

I am currently facing the challenge of loading from our database the packing_plans - plans on how a certain item is to be packed - for a month in the past.

I cannot use the logic we already have  because that keeps the most current plan per item_no only - so I have to build the logic from scratch.

Maybe someone can help me along a little bit here: I have done similar things before, but I'm not quite sure:

- Every plan has a Date_From (when it was activated) and a Date_To (up to which it was valid, after which there should be a new one);

- I have started with September 16: To begin with, I have loaded all the plans with a Date_from up to September 30 and with a Date_to

  starting September 01;

- The target is, for every given day in september 16 and for every item_no, to have only one plan;

=> I will store the results as a qvd once I have that and make it into an app running once a month and doing just that. If there's not too

     much data, I can have a DATE_field that I can use to link or join this to another table.

I'll be back here once I have made some progress on reducing the nr. of concurrently active plans ...

Best regards,

DataNibbler

[datanibbler]

Hi,

now I have progressed a bit - I have thrown out the old DATE_TO that there was because it was populated in a very confusing way.

Instead I made a new one myself based on the Date_From of the successor of a plan:

- I ordered the entire table by the general Key ascending and the Date_From descending so that the newest one

  would come first in the table.

=> Then I generated a new field in another resident LOAD saying

>> IF(PREVIOUS(Key) = Key, (PREVIOUS(Date_From)-1, Makedate(9999,12,31)) <<

=> Now I have a very strange phenomenon: I do have reports going in the correct order - for instance, one is valid from Feb 02, 2016 to May 02, 2016 and the next one becomes valid on May 03, 2016 - but I also have one or several which are valid from Feb 02, 2016 to Feb 01, 2016 according to my fields??!

Very strange ...

[datanibbler]

Ah - I get it - I had a lot of duplicates before which probably did differ in some tag - which is not relevant in this instance ... so they all had the same DATE_FROM, several records in a row - so the formula used the DATE_FROM from the previous row - which in all but one instance was probably the same as in the current row - and subtracted 1, just as I had written it ... so, once again it's not a bug, it's just the behaviour I actually wanted ...

[datanibbler]

Okay,

now I have more or less what I want: Just one packing_plan active for one timerange, all within one month - September 2016 in this instance, but I will make that into a variable lateron.

Now I could link up a standard mastercalender that we used to have so that every single day is available and then I guess I need some IntervalMatch to link it ...

OR

Maybe there is an easier, albeit less elegant alternative: To use AUTOGENERATE to duplicate one packing_plan for every day between its DATE_FROM and its DATE_TO - only within that month of course, I don't want every day up to the year 9999 ...

How would I do that?

I need a loop over all the General_Keys and use a WHILE loop inside that - I'll try it out for just one General_Key so I can see the results promptly ...

[datanibbler]

O_O - not so easy ...

I now have one packing_plan which was activated on Feb 02, 2016 and is valid till the year 9999 - that means need two IF_clauses - I want to generate every day in September 2016 only, none earlier and certainly not till the year 9999 ...

[datanibbler]

Hi,

I have implemented IF_clauses now to close off intervals: In case the Date_from in the first interval is before Sep 01, then the variable should default to Sep01, otherwise it should be filled with the value from my Counter_table.

Likewise, there is an If_clause for the Date_To - if that is after Sep30, the variable should default to Sep30, otherwise it should be filled with the value from my Counter_table.

something is strange: When I shorten my loop so that the loop only parses my special counter_table and I get a MsgBox with the Date_From and Date_To_values, then it appears correct - in this particular instance, there are three records there, with three different intervals, all within September 2016.

When I open up the loop and do all the other operations - taking one interval at a time and filling up the days, autogenerating one record for every day between Date_From and Date_To - then, upon the second iteration, my IF_clause fails and my variable for the Date_From defaults to Sep 01. Likewise, the variable for Date_To defaults to Sep 30.

Maybe someone here can help me on that? I'll post my code in the attached file.

[datanibbler]

Ah - it seems I have used the PEEK() function in the wrong way - the help file says, the name of the field should be in quotes, the name of the table not - but I think I remember I have come across that problem before, they both have to be in quotes, now it seems to be working ... let's see ...