topic Re: Codes of count but the value is incorrect in App Development

Codes of count but the value is incorrect

Sxbbb — Fri, 15 Nov 2024 21:36:21 GMT

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.

Re: Codes of count but the value is incorrect

sidhiq91 — Mon, 10 Jul 2023 02:58:26 GMT

@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.

Re: Codes of count but the value is incorrect

Sxbbb — Mon, 10 Jul 2023 03:05:48 GMT

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.

Re: Codes of count but the value is incorrect

marcus_sommer — Mon, 10 Jul 2023 07:18:48 GMT

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.

Re: Codes of count but the value is incorrect

Sxbbb — Mon, 10 Jul 2023 07:43:56 GMT

How do I solve the problem?

This is Edit Script

Re: Codes of count but the value is incorrect

marcus_sommer — Mon, 10 Jul 2023 07:56:31 GMT

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);

Re: Codes of count but the value is incorrect

Sxbbb — Mon, 10 Jul 2023 07:58:54 GMT

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.

Re: Codes of count but the value is incorrect

marcus_sommer — Mon, 10 Jul 2023 08:22:38 GMT

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