topic Re: Count ignoring joins in QlikView

Count ignoring joins

Fri, 03 Jan 2014 12:29:18 GMT

I have a model which contains multiple tables that are related. I need to do a count of all the rows in one of these tables without this being impacted by its relationship/join to any of the other tables. Is it possible to count the rows in a single table ignoring any joins?

Hope this makes sense?!

Thanks,

Tim

Re: Count ignoring joins

maxgro — Fri, 03 Jan 2014 12:38:13 GMT

If I understand your question, try to add a field NumRecord in the load script for that table

Table:

Load

...,

....,

1 as NumRecord

;

SQL

...

;

in chart you can use sum(NumRecord) or sum({1} NumRecord) to count record

Re: Count ignoring joins

Fri, 03 Jan 2014 12:49:15 GMT

you can also use a variable in your script like count(*) as R from your table to get records number and use a variable

LET v = peek('table', 0, 'R');

So $(v) will always equal count(*)

Best regards

Chris

Re: Count ignoring joins

Fri, 03 Jan 2014 13:18:06 GMT

Hi Time,

I Hope you try this

LOAD NoOfRows(Dev01) as No_Rows Resident Dev01;

LOAD Peek(Region,-1,'Dev01') as No_rows1 Resident Dev01;

LOAD Count(Region) as no_Cnt Resident Dev01;

Re: Count ignoring joins

brijesh1991 — Fri, 03 Jan 2014 13:28:30 GMT

On Backend:

Table1;

Load

FieldList,

'Table1' as Table1Flag from ......

Table2:

Load

FieldList,

'Table2' as Table2Flag from .....

----------

On FrontEnd: Take a Text Object: Count(distict ID)

Take Listbox: Table1Flag and Table2Flag

Now if you want to see only Table1's count of Id, then select Table1Flag(Make sure Table2Flag is clear)

Do the same for Table2

Re: Count ignoring joins

preminqlik — Fri, 03 Jan 2014 14:35:23 GMT

load *,

rowno() as rowID

from xxx

---> count(rowID)