topic Re: how do i count distinct record in a loaded data and set the number of record to a variable after data loaded in QlikView

how do i count distinct record in a loaded data and set the number of record to a variable after data loaded

Wed, 19 Jul 2017 08:44:00 GMT

Dear all,

I have a data table loaded from CSV as shown below. As you can see the field 'No' is repeated. How do i do the distinct count on that table based on the filed 'No'. and after that update a variable with this distinct count no?

Re: how do i count distinct record in a loaded data and set the number of record to a variable after data loaded

Wed, 19 Jul 2017 08:53:05 GMT

or i can use maxstring(No) to retrieve the maximum number is '9'.

then how do i set this '9' to a variable created after data loaded?

Re: how do i count distinct record in a loaded data and set the number of record to a variable after data loaded

PrashantSangle — Wed, 19 Jul 2017 08:57:27 GMT

Hi,

use system fields i.e.

$Table and $ Rows will give you required result.

Regards

Re: how do i count distinct record in a loaded data and set the number of record to a variable after data loaded

Wed, 19 Jul 2017 08:59:10 GMT

ok. If want to to the maxstring(No) during the load script possible?

As i have a variable named 'vSheets' and during the load script. i want to set the vSheet to become maxstring(No)

Re: how do i count distinct record in a loaded data and set the number of record to a variable after data loaded

effinty2112 — Wed, 19 Jul 2017 09:02:31 GMT

Hi Chee,

try this in your script after the csv has been loaded

CountNo:

LOAD

Count(DISTINCT No) as CountNo

Resident Data;

Let vCountNo = Peek('CountNo',0,'CountNo');

Cheers

Andrew

Re: how do i count distinct record in a loaded data and set the number of record to a variable after data loaded

effinty2112 — Wed, 19 Jul 2017 09:07:20 GMT

Chee,

This is simpler than my first suggestion:

Let vCountNo = FieldValueCount('No');

Re: how do i count distinct record in a loaded data and set the number of record to a variable after data loaded

Wed, 19 Jul 2017 09:07:35 GMT

quite close. can i do some calculation within the peek? for example i want to minus Countno with 1?

Re: how do i count distinct record in a loaded data and set the number of record to a variable after data loaded

effinty2112 — Wed, 19 Jul 2017 09:08:44 GMT

Hi Chee,

Try:

Let vCountNo = FieldValueCount('No') -1;