Load, join, WHERE

[#1]

Hi,

I need to LOAD new records ONLY:

WHERE TimeStamp>= vUpdateDate

However, since I first have to make a join in order to get the TimeStamp and complete my table I cannot use a regular WHERE statement, or can I?

Any ideas how to work around this? Can I create a "Final Table" including the TimeStamp and then use a WHERE statement?

Im lost

Thanks in advance,

Olle 

NoConcatenate
LOAD CCY,
           ACC,
           BIC,
           ValueDate

FROM $(vSourceData)*.xls
(biff, embedded labels, header is 3 lines, table is @1)
WHERE Len(currency)=3;

DateTimeInfo:
LOAD
     Timestamp#((DATE(@1, 'MM/DD/YYYY')&' '&TIME(@2, 'hh:mm:ss')),'DD/MM/YYYY hh:mm:ss') As TimeStamp
FROM $(vSourceData)*.xls
(biff, no labels, header is 1 lines, table is @1) Where RecNo() < 2;

JOIN(Incremental)
LOAD * RESIDENT DateTimeInfo;
DROP TABLE DateTimeInfo;

[simondachstr]

For this I would need a sample of your whole loading script

Make sure $(vUpdateDate) is evaluated properly.

Add a TRACE '$(vUpdateDate)'; and check the script log to make sure it does.

[#3]

Hi Martin,

I checked $(vUpdateDate) and it looked ok.

Please find attached a sample file with my entire load script.

Also, I've assembled 3 sample reports to load data from.

As mentioned before, there's synthetic key issues.

Thanks in advance!

Kind Regards,

Olle

[#4]

Hi Martin,

hope you are well!

Did you have a chance to have a look at my script yet?

Any help would me much appreciated!

Kind Regards,

Olle

[simondachstr · Accepted solution]

Apologies for the late reply!

See attached. There were a couple of syntax errors (always end your trace statement with a ; ). I've tested the script by creating a new Report4.xls and changing the date to T+1 and the incremental load worked. Make sure you change your vSourceData and vSaveQVD variables back to your File-Path as I've modifed them in the script.

Let me know if it works okay for you.

[#6]

Works great, thanks a lot Martin!

Kind regards,

Olle