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..

[sfatoux72]

Ok,

Load only Loc: table and verify if the field Location contains some multiple value (add a selection list for Location field, add frequency and sort by frequency )

[nareshthavidishetty]

Hi,

Below the result i have got after loading only LOC Table,

Thanks..

[sfatoux72]

I can just see the different value of Location, you didn't add the frequency as I ask you !  

Please, display frequency and sort by frequency descending

    

To have this type of result

[nareshthavidishetty]

Hi,

Below is the result,

Thanks..

[Colin-Albert]

I would guess that some of your joins have multiple matches on the key fields and so are creating multiple records in your table. A better approach would be to use mapping tables which always return the first matching record and avoid creating duplicate rows.

Have a look at these blogs

Qlik Design Blog : Don't join - use Applymap in... | Qlik Community

To Join or not to Join

[sfatoux72]

Ok,

We could see that for example Location 047 has 136008 rows in Loc: table, that signify that the value of APP# will be multiply by 136008  due to the join.

To illustrate:

APP:

LOAD *

Inline [

  id, APPL#, location

  1, 20, AAA

  2, 30, BBB

];

If I join data with multiple rows for each Location (3 different for AAA and 2 identical for BBB)

Left join(APP)

Loc:

LOAD *

Inline [

  location, Address

  AAA, abc

  AAA, def

  AAA, hij

  BBB, xyz

  BBB, xyz

];

The value for ID 1 corresponding to Location AAA is multiply by 3 and ID 2 corresponding to Location BBB is multiply by 2

It could be several reason to have multiple rows for one location :

1. Location is not a key (not identify a Location)
2. Location is a key but repeated in the source table due to historisation. Other field like address are different  ==> AAA
3. The unique Location in the source table is repeated due to other values not specific to the location (fact table)  ==> BBB

If it's the 1st point, you need to find the Key

If it's the 2nd point, you need to find the correct rows.

For the 3rd point, just add Distinct on your script to have only one row per Location. I thing it's this point according to the number of rows.

Left join(APP)

Loc:

LOAD Distinct *

Inline [

  location, Address

  AAA, abc

  AAA, def

  AAA, hij

  BBB, xyz

  BBB, xyz

];

You see that the sum for ID 2 is correct now.

[nareshthavidishetty]

Hi Sebastian,

What your saying is correct we can you use Distinct (APP# ) to get unique numbers.

Scenario:

Every customer has one unique ID = APP#

One customer can apply for a loan more than 1 attempt in a day.

Here we were trying to capture one APP# per customer in a day.So fro this we were using Count(DISTINCT APP#).

But the user want all APP# count irrespective of applying number of times.

Here after removing the distinct the actual count is showing more but when we load that table alone it is showing correct and it's not correct after joined with other table.

Primary Key in the Model : Location

Thanks..

[nareshthavidishetty]

Hi,

Please ignore Primary Key in the Model : Location in the above post.Below were the fields,

Thanks..

[sfatoux72]

I never ask you to modify your expression by adding Distinct, but you need to join only table with one row per join keys (use Distinct is it resolve duplicate keys).

Try to load your table without the join.

[nareshthavidishetty]

Hi,

When loading table with out join gives correct count.

Thanks..