Having trouble with loading a data

[#1]

Hi everyone,

I have the following script that combines serveral data sources into one fact table, like this:

OpenItemsTemp:

LOAD CRDATTIM,
CRDATE,
EVNTTIME,
EVNTNSECS,
EVENTCODE,
UNITCD,
WRKTYPE as ProcessName,
STATCD,
QUEUECD,
Right(INXFLD01,1)&''&Num(Left(INXFLD01,9)) as SchemeNo,

UniqueKey;
SQL SELECT *
FROM FPWFI.AWDFPPWH.BI0001;

Left

Join(OpenItemsTemp)
LOAD UniqueKey,
'N' & DATAVALUE as NGSSchemeNo;
SQL SELECT *
FROM FPWFI.AWDBSPDB.W01U999S;

Now I have the fact table loaded, I want to load that again but only if SchemeNo starts with an 'F' or NGSSchemeNo starts with an 'N'. This is the bit I'm getting stuck with, I've tried a resident load but I can't get it to work, any suggestions?

[Anonymous]

Tmp:

noconcatenate load *

resident OpenItemsTemp

where left(NGSSchemeNo,1)='N' and left(SchemeNo,1)='F')

drop table OpenItemsTemp

rename table Tmp to OpenItemsTemp

[alexandros17]

After you load do:

OpenItems:

noconcatenate

Load * resident OpenItemsTemp where Left(SchemeNo,1)= 'F' or Left(NGSSchemeNo,1)='N';

Drop table OpenItemsTemp;

Let me know

[MarcoWedel]

Hi,

you might also append these lines to your script:

Right Join (OpenItemsTemp)
LOAD UniqueKey
Resident OpenItemsTemp
Where SchemeNo like 'F*' or NGSSchemeNo like 'N*';

hope this helps

regards

Marco

[#5]

Thanks for the help chaps, it's working as expected now.

I was missing the no conatenate in what I was trying to get to work!

[MarcoWedel]

Good to hear.

Please go ahead and close this thread.

thanks

regards

Marco