topic Re: Create field based on count in script in QlikView

Create field based on count in script

tinapullen1 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Create field based on count in script

rubenmarin — Sat, 01 Feb 2020 18:19:34 GMT

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.

Re: Create field based on count in script

Brett_Bleess — Mon, 24 Feb 2020 22:02:02 GMT

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