Create field based on count in script

[tinapullen1]

Hi,

I have created a script to see how many of each order no. there are - this works (example 1 below) .  I now want to add a formula in script so that if the [Order count] equals 1 & the [Load. point (Name1)] equals a 'specific location' then to identify as whether to include or exclude but I can't seem to work out how to do it.  Can anyone help please?  Thanks in advance

Example 1

Table1:

Load

[Ext. order number] as [Order no.],

[Load. point (Name1)],

From location;

Table2:

Load

[Order no.],

Count(Order no.]) as [Order count]

Resident table1 group by [Order no.]

[rubenmarin · Accepted solution]

Hi, maybe you are looking to something like this?:

Table1:
Load
[Ext. order number] as [Order no.],
[Load. point (Name1)]
From location;

Table2:
LOAD
  [Order no.],
  [Order count],
  If([Order count]=1 and checkLoadPoint='Specific Location', 1, 0) as flagOrder // This to flag and exclude/include using set analysis
where [Order count]<>1 or checkLoadPoint<>'Specific Location'; // this to exclude, maybe you don't want it here, it's just to show possibilities
Load
[Order no.],
MaxString([Load. point (Name1)]) as checkLoadPoint
Count(Order no.]) as [Order count]
Resident table1 group by [Order no.]

The 'where' clause in the preceding load is optional, just to show how can be used.

[Brett_Bleess]

Did Ruben's post get you what you needed for your use case?  If so, please do not forget to return to your thread and on his post, use the Accept as Solution button to give him credit for the help and let other Members know that worked for you.  If you did something else, consider posting that and mark it to close the thread, and if you are still working on things, please leave an update with what you still need.

Regards,
Brett