Below is the script we were using but here we are calculating COUNT(APPL#) and we have this value with database it showing wrong but if just load the APP table alone it shows the right value.
Please let me know if the is wrong
APP:
LOAD
date((FCFSDT),'MM/D/YYYY') as SUB_Date,
FCFSDT,
COMP,
FCAACD as Location,
APPL# ,
FCALNB as CustomerID,
[FCK0ST],
pick(match([FCK1ST],'R','W'),'In-store','Offsite Site (Web+Ph+Mail)') as Channel,
pick(match([FCK0ST],'A','C','D','I','Q','U','W'),'Approved','Canceled','Declined','In Store','queued','Used','Waiting') as App_Status,
pick(match([COMP],'CON','GEM'),'Conns Application','GE Application') as App_Category,
Year(Date(AddYears(Date(Date#('19' & Right(FCFSDT,6),'YYYYMMDD'), 'DD/MM/YYYY'), 100 * Left(FCFSDT,1)), 'M/D/YYYY')) as Year
Date(AddYears(Date(Date#('19'&Right(FCFSDT,6),'YYYYMMDD'),'MM/DD/YYYY'),100*Left(FCFSDT,1)),'M/D/YYYY') as App_Date
;
SQL SELECT *
FROM AS400D75.STOPRE.GRETYYUwhere FCFSDT>='1140101';
left join (APP)
Loc:
LOAD //ADABCD as CompanyCode,
ADAACD as Location,
ADAUNB as Address,
ADASTX as LocationDesc,
//ADAGST as LocationType,
//ADBEST as SubLocaInd,
ADACCD as District
;
SQL SELECT *
FROM AS400D60.DISPDBF.DSADREP;
left join (APP)
Con:
LOAD ACA1CD as Country,
// ACANTX as Address1,
// ACAOTX as address2,
ACAPTX as Zipcode,
ACAUNB as Address
;
SQL SELECT *
FROM AS400D70.HEDFGS.POIUJC;
left join (APP)
State:
LOAD AEADST as State,
AEAPTX as Zipcode,
AEAQTX as City,
AEARTX as County;
SQL SELECT *
FROM AS400D60.YRDGSF.LKJHGBV;
RACAPPLI:
LOAD APPL# as racApplicationNbr,
[COMP] as raccomp,
App_Category as racAppCategory,
Location as racLocation,
CustomerID as racCustomerID,
FCFSDT as racFCFSDT,
App_Status as racAppStatus,
Channel as racChannel,
// Month as racMonth,
LocationDesc as rsalocdes,
App_Date as racApp_Date,
State as RACState,
Zipcode as RACZipCode,
City as RACCity
RESIDENT APP
WHERE App_Status='Declined'and App_Category='Conns Application' ;
