topic Adding Date in QlikView

Adding Date

Thu, 19 May 2011 17:51:47 GMT

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

Adding Date

martinpohl — Thu, 19 May 2011 18:01:03 GMT

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

Adding Date

Thu, 19 May 2011 18:25:57 GMT

Thanks Martin - worked!!!

Stephen