Data Model

[nareshthavidishetty]

Hi,

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' ;

inner  join (RACAPPLI)

RACCust:

LOAD

    C4OZXT ,

    C4OVXT,

    Left((C4OZXT), Len(C4OZXT)-8) as racCustomerID

    ;

SQL SELECT *

FROM AS400D60.IKUJYHTG.CAC4CPP;

left join(APP)

LOAD LOCATION as Location,

    Market

FROM

(ooxml, embedded labels, table is Sheet1);

left join( RACAPPLI)

LOAD LOCATION as racLocation,

    Market as racmarket

FROM

(ooxml, embedded labels, table is Sheet1);

SSS:

left join(APP)

LOAD  Location,

    SSS_Indicator

FROM

(qvd);

SSS:

left join(RACAPPLI)

LOAD  Location as racLocation ,

    SSS_Indicator as racSSS_Indicator

FROM

(qvd);

Thanks..

[puttemans]

Is it possible that by joining other tables, you multiply the appl# field, and thus when you count them, you get a different outcome?

You could compare by loading the APP table alone, and select one line. Then add the loads, and see whether and where the one line splits into multiple ones.

Regards,

Johan

[Digvijay_Singh]

Not sure though but I am not finding matching field here with APP while joining

left join (APP)

Con:

LOAD ACA1CD as Country,

  // ACANTX as Address1,

  // ACAOTX as address2,

  ACAPTX as Zipcode,

    ACAUNB as Address

May be some joins are creating cartesian join which may create extra records

[nareshthavidishetty]

Hi,

Am getting right count when load APP table alone but when i load APP table with only

left join (APP)

Con:..............

Exit script;

the values were wrong

Thanks..

[sfatoux72]

The matching field is Address that is added in APP table with the previous join ...

[Digvijay_Singh]

Are you saying after first left join count is wrong? (On another note 'Con:' is ineffective here as you are joining here to APP so new table name won't be required and won't work)

If Yes, then What I wanted to confirm if you have any joining field from this table with APP table, I didn't see Country,Address etc field in APP, so will be cartesian join which will create huge no of extra records and would impact all totals.

[sfatoux72]

You cannot join only con: table, because there is not matching key like said by Digvijay.

What happen if you load APP, join of Loc: and join of Con: ?

[nareshthavidishetty]

Hi,

After load APP, join of Loc: and join of Con:

the count is not correct.

Thanks

[sfatoux72]

If th count is OK when you join Loc: and not ok when you add join of Con: , that signify that your Con: table returned multi line for some address (your matching key).

Try to load only Con: table and verify if the field contains some multiple value (add a selection list for address field, add frequency and sort by frequency )

[nareshthavidishetty]

Hi,

The count is not ok even when i joined LOC.

When i joined APP(Main table) with any other table the count is not correct.

Thanks..