Hey everyone, i want to know of all Sales order items how much of them are affected by change price, i dont know how to put it into a variable. In general i dont know how to have the inner join of a count_table and an activity, thanks in advance

Hi @Louis Forster,

If you want to count how many sales order are affected by a price change (considering this is an activity), you can use MATCH_ACTIVITIES Together with a SUM. MATCH_ACTIVITIES returns an integer column, which flags all matching cases with 1 and all non-matching cases with a 0.

It would look something like this.

SUM(CASE WHEN MATCH_ACTIVITIES("YOUR_ACTIVITY_TABLE"."ACTIVITY_COLUMN", NODE['your change price activity name']) = 1 THEN 1 ELSE 0 END).

This sums up the amount of cases that will have a price change.

-------

Second, to make a variable, do you want to use a variable in an analysis or view? If you are using a view, use a knowledge model to create an KPI with this code.

If you are using an analysis follow the below steps.

Enter the pql statement from earlier in the static value field.

Hopefully this helps you out!

unfortunately i don't know how to convert my Table VBAP (my Sales Documents item data) into an activity table . It throws me the error message "Input table ["VBAP"] is not defined as an activity table. Process operators can only be used when a process is configured." if i use your formula

Hi @Louis Forster,

Right, this means that VBAP is not set as your activity table in your data model.

In your data model, the activity table is set with a letter 'A', like below.

To begin, which tables do you have access to? do you know which table is your activity table?

i am pretty sure i have access to all tables, but i dont know my activity table

@Louis Forster,

When you create an KPI in your analysis. Which tables do you see? You can see them on the left hand side.

If we cannot determine from here what your activity table is, I advise you to go to Data Integrations and seek out the activity table there as mentioned in my last response.

there are a lot of tables including the VBAP: Sales Document Item Data

also i looked up in the data model and VBAP is also there

@Louis Forster,

Okay, do you see a Table marked with an 'A' like in my previous response? That is your activity table.

ok perfect and how do i use it know to see all the VBAP that are affected by change price.

i got it know, thanks a lot

No problem! @Louis Forster :)