topic Re: count the record in QlikView

count the record

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

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

Re: count the record

swuehl — Mon, 14 Mar 2016 13:09:14 GMT

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.

Re: count the record

swuehl — Mon, 14 Mar 2016 13:12:05 GMT

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