topic Re: Count values in a different table in QlikView

Count values in a different table

Thu, 28 Feb 2013 12:16:39 GMT

If I have a table loaded (e.g. name 'Outlook') and during the load of a second table (e.g. 'Test') I want to count the number of records that exist in 'outlook' how can i do this? Do you have an example?

Both tables have an attribute by the name of 'SDS_ID'.

Thanks

Re: Count values in a different table

fdelacal — Thu, 28 Feb 2013 12:25:48 GMT

in script you can add

rowno() as NunLine

that way you have that you are looking for..

HOPE it helps you

regards.

Re: Count values in a different table

Thu, 28 Feb 2013 12:29:23 GMT

Would I put that as part of the second table load? if so, how can I use the rowno to look into table1?

Re: Count values in a different table

Gysbert_Wassenaar — Thu, 28 Feb 2013 12:31:23 GMT

I think you're not interested in the total rows of table Test, but a count of related records based on SDS_ID. For that you first need to join the two tables. You can then calculate a count using the combined table. Or you could calculate the counts in the UI with expressions in charts instead of in the script.

Here's a simple example for the script:

Test:

Load SDS_ID, a,b,c

from ...table1...;

join Load SDS_ID, e, f

from ...table2...;

Counts:

Load SDS_ID, count(a)

resident Test

group by SDS_ID;

Re: Count values in a different table

Thu, 28 Feb 2013 12:32:31 GMT

Hello,

If you are looking for the total row count, use below code.

TMP:
LOAD * INLINE [
    YEAR, VALUE
    2013, 1200
    2012,1400
];

TEMP:
LOAD
YEAR AS YEAR,
YEAR AS YEAR_1,
VALUE/12 AS NEWVALUE,
recno() AS RecordCount
RESIDENT TMP;

LET Count = PEEK('RecordCount',-1,'TEMP');
DROP TABLE TMP;