topic Re: Data Model in QlikView

Data Model

nareshthavidishetty — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Data Model

puttemans — Thu, 17 Mar 2016 13:44:32 GMT

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

Re: Data Model

Digvijay_Singh — Thu, 17 Mar 2016 13:53:58 GMT

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

Re: Data Model

nareshthavidishetty — Thu, 17 Mar 2016 13:58:30 GMT

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

Re: Data Model

sfatoux72 — Thu, 17 Mar 2016 14:03:53 GMT

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

Re: Data Model

Digvijay_Singh — Thu, 17 Mar 2016 14:07:12 GMT

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.

Re: Data Model

sfatoux72 — Thu, 17 Mar 2016 14:08:50 GMT

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: ?

Re: Data Model

nareshthavidishetty — Thu, 17 Mar 2016 14:25:25 GMT

Hi,

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

the count is not correct.

Thanks

Re: Data Model

sfatoux72 — Thu, 17 Mar 2016 14:44:38 GMT

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 )

Re: Data Model

nareshthavidishetty — Thu, 17 Mar 2016 14:56:50 GMT

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

Re: Data Model

sfatoux72 — Thu, 17 Mar 2016 15:00:30 GMT

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 )

Re: Data Model

nareshthavidishetty — Thu, 17 Mar 2016 15:14:09 GMT

Hi,

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

Thanks..

Re: Data Model

sfatoux72 — Thu, 17 Mar 2016 16:44:16 GMT

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

Re: Data Model

nareshthavidishetty — Thu, 17 Mar 2016 18:28:37 GMT

Hi,

Below is the result,

Thanks..

Re: Data Model

Colin-Albert — Thu, 17 Mar 2016 18:34:56 GMT

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

Re: Data Model

sfatoux72 — Thu, 17 Mar 2016 23:02:29 GMT

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

];

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 :

Location is not a key (not identify a Location)
Location is a key but repeated in the source table due to historisation. Other field like address are different ==> AAA
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

];

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

Re: Data Model

nareshthavidishetty — Fri, 18 Mar 2016 11:07:20 GMT

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

Re: Data Model

nareshthavidishetty — Fri, 18 Mar 2016 11:11:41 GMT

Hi,

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

Thanks..

Re: Data Model

sfatoux72 — Fri, 18 Mar 2016 12:18:47 GMT

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.

Re: Data Model

nareshthavidishetty — Fri, 18 Mar 2016 12:56:17 GMT

Hi,

When loading table with out join gives correct count.

Thanks..