Adding Date

[#1]

I have a table (Table 1) that we get once each night that has Sales Order Number and the date the order was entered. We have a routine that runs each hour that returns a table (Table 2) of oonly open orders but does not have the field order entry date (and at the moment we can't add it to the routine). So when I load the two tables with the link Sales Order Number I get no order enty date for open orders that are entered today because they are not in Table 1.  I load table 2 first and use a left keep on Table 1 (as that table has all orders open or closed) as I only want matches from Table 1 to Table 2.  How in the load script can I create a date of today for items that don't have a match in Table 1 but are in Table 2.

Thanks,
Stephen

[martinpohl · Accepted solution]

load Table1

join Table2 with the Enterdate by the field Sales Order Number.

Then:

load

Sales Order Number,

if(isnull(Enterdate),today(),Enterdate) as Enterdate

resident Table1;

drop Table1.

So you will join those SON which are having a date. Then in the secon statement you load the SON an check the Enterdate. If it is null (no date) you set today, otherwise the Enterdate.

Regards

[#3]

Thanks Martin - worked!!!

Stephen