count the record

[Anonymous]

Hi Qlikview team

I have load the below tables and written below script. i have to keep my duplicates in tables.

when i count the record it  shows 6 but i load 9 records. when i apply left join with distinct keyword records reduced in main table why.

ACCT	BA or SE	PC	GRPACCT
0020330000	2M00	P1754	2033
0014100000	GRP		1410
0014101500	GRP		1410
0014101200	GRP		1410
0014101200	GRP		1410
0014102600	GRP		1410
0014102600	GRP		1410
0024309000	GRP		2430
0024309000	GRP		2430

Tab:

LOAD

     '201601' as FiscalYrMth,

     ACCT,

     [BA or SE],

     PC,

     GRPACCT,

     Location  

FROM

(ooxml, embedded labels, table is Sheet1);

Left Join

LOAD

Distinct

FiscalYrMth,

FiscalYrMth as RecID

Resident Tab;

regards

Ramu

[swuehl]

The DISTINCT predicate will apply also to JOINed and CONCATENATEd tables, i.e. it will be kept as a property of the resident table, that's a Qlik speciality.

[swuehl]

Try instead a WHERE NOT EXISTS() clause:

Tab:

LOAD

     '201601' as FiscalYrMth,

     ACCT,

     [BA or SE],

     PC,

     GRPACCT,

     Location  

FROM

(ooxml, embedded labels, table is Sheet1);

LEFT JOIN

LOAD

FiscalYrMth,

FiscalYrMth as RecID

Resident Tab

WHERE NOT EXISTS(RecID, FiscalYrMth);