Codes of count but the value is incorrect

[Sxbbb]

I have Field 

I want to count BoardNo_ORB  But I tried the code : Count(BoardNo_ORB) Invalid result

The correct result is 13.

I want to count BoardNo_ORB  But I tried the code : Count(BoardNo_ORB) Invalid result. The correct result is 13.

[sidhiq91]

@Sxbbb Could you please post the sample data please?

Ideally count(distinct BoardNo_ORB) should give you the right result.

But let me look into your data.

[Sxbbb]

I want to count BoardNo_ORB   The correct result is 13.

 But I tried the code : Count(BoardNo_ORB) . Result is 65 invalid value

The correct result is 13.

[marcus_sommer]

Each object within the UI displayed the distinct combination of field-values. It's not the base for a calculation else the result.

In your case it means that you haven't a single value of BoardNo for a WorkOrder else there are 5 field-values. This is cause from either invalid data or wrong script- and/or data-model logic - probably by wrong designed joins/associations.

[Sxbbb]

How do I solve the problem?

This is Edit Script

[marcus_sommer]

I assume that the duplicates come directly from your load by overlapping of the file-content between those multiple files. You may try to resolve such case with:

load distinct ...;

Another approach would be to check for an unique key by loading the data. If none such key exists it might be create-able by combining several fields, for example with something like:

load *, F1 & '|' & F2 as Key
from Source where not exists(Key, F1 & '|' & F2);

[Sxbbb]

What if I want to load an Excel file with just the latest date? What can I do?

 But Excel files will be added every day.

[marcus_sommer]

You may directly grab the latest file with something like:

let v = text(date(today()-1, 'YYYYMMDD'));

load * from [YourPath\$(v)_40_ORB.xlsx] (ooxml, ...);

On top of such logic you will probably need some error-handling for the case the file didn't exists (too late or on general missing and/or weekends/holidays) - maybe with ERRORMODE.

Better are mostly approaches with a filelist() loop which would run through entire folder and within another if-loop checked the filetime() or a file-name extract or the date-field within the load against the latest previous data which is stored within a variable. It would go in this direction:

for each file in filelist('path\*.xlsx')
   if filetime('path\*.xlsx') > '$(v)' then
       load ... from [$(file)] (ooxml, ...);
        let v = filetime('$(file));
   end if
next