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

[#1]

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?

[#2]

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?

[PrashantSangle]

Hi,

use system fields i.e.

$Table and $ Rows will give you required result.

Regards

[#4]

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)

[effinty2112]

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

[effinty2112 · Accepted solution]

Chee,

This is simpler than my first suggestion:

Let vCountNo = FieldValueCount('No');

[#7]

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

[effinty2112]

Hi Chee,

Try:

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