Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Tmp:
noconcatenate load *
resident OpenItemsTemp
where left(NGSSchemeNo,1)='N' and left(SchemeNo,1)='F')
drop table OpenItemsTemp
rename table Tmp to OpenItemsTemp
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
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
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!
Good to hear.
Please go ahead and close this thread.
thanks
regards
Marco