Add new record (date) and capture date of record before it

[crystles]

I have a field with New and Old Codes and a date that the codes changed to the new code.

Code Table fields

   PartNumber,

   OldCode,

   NewCode,

   CodeChangeDate

Now, I want to create a single table to have these specific fields.

   PartNumber,

   Code

   Date Changed

The code field will have, for each part number, the record for the min Change date will have the old code and a Change date of 01/01/2000 and the next record will have the Original Change Date and the New code of that same record, and so on...

I have been able to get the first part accomplished, but since I am using Previous, it doesn't capture the last Change date for that part.

Here is that code, that I have now. ( I also have a recNo to sort the fields by that is in the resident table, and a Minimum Change Date for each part)

ChangeCodeTEMP:
LOAD
PartNumber,
Code_Date,
if(ChangeCodeMIN=Code_Date, MakeDate(2000,01,01),Previous(Date(Code_Date))) as ChangeCodeDate;
Load *
Resident OldNewCodes_TEMP
Order By RecNo
;

[sunny_talwar]

I think it would be helpful if you can share some data with the desired output to offer better help. Personally, my mind works better when I see numbers and not just plan test

[crystles]

Yes, after I posted this, I reread it and realized it is probably very confusing, sorry! I am the same, visual representations always work better for me too

OK, so here is a mock up an example of how I would like the data to be before and after for a sample part number.

The first table is how the data looks now. The second table is how I actually want the data in the table to look.

I tried color coding the fields so you could see where the same field is in each table. The green cell is not in the current table because I am creating that field to give historical data for that part.

... I hope I didn't make it more confusing

[sunny_talwar · Accepted solution]

May be all you need to do is to Concatenate to your existing table like this

Concatenate (OldNewCodes_TEMP)

LOAD PartNumber,

           CodeOLD as CodeNEW,

           Date(MakeDate(2000, 1, 1)) as Code_Date

Resident OldNewCodes_TEMP

Where Code_Date = ChangeCodeMIN;

[annafuksa1]

try

[Code Table]:

load 

   PartNumber,

   OldCode,

   NewCode,

   CodeChangeDate

   From ...;

left join

ChangeCodeTEMP:

LOAD

1 as LastChange,

PartNumber,

max(Code_Date) as Code_Date

Resident OldNewCodes_TEMP

group by PartNumber

;

FinalTable:

load

PartNumber,

NewCode  as Code,

CodeChangeDate as [Date Changed]

Resident [Code Table]

where LastChange = 1;

[crystles]

This worked perfectly! Thank you!

I always tend to over complicate my code when a simple elegant solution like this would be easiest, lol

Thanks again!