Merge or Combine

What is the pql query you are using for Lead Time A and Lead Time B ?

Thank you for your answer.
 

First Query:
CASE

    WHEN left("o_celonis_MaterialMasterPlant"."ID", 3) = 'P20'

    AND "o_celonis_MaterialMasterPlant"."PlannedDeliveryTimeDays" IS NOT NULL

    AND "o_celonis_MaterialMasterPlant"."PlannedDeliveryTimeDays" != 0

    THEN "o_celonis_MaterialMasterPlant"."PlannedDeliveryTimeDays"
END

Second Query:

CASE

    WHEN left("o_celonis_MaterialMasterPlant"."ID", 3) = 'P18'

    AND "o_celonis_MaterialMasterPlant"."PlannedDeliveryTimeDays" IS NOT NULL

    AND "o_celonis_MaterialMasterPlant"."PlannedDeliveryTimeDays" != 0

    THEN "o_celonis_MaterialMasterPlant"."PlannedDeliveryTimeDays"

END

FYI:

MaterialMasterPlant.ID contains a mix of IDs starting with 'P18' and 'P20'. Since I only want to extract the first 3 characters, I used left…

I have created a separate column for P18 and another column for P20.

For better readability, I labeled the columns as Lead Time A and B and Price A and B here as a sample but the correct ones in my view are Lead Time P18/P20 and Price P18/P20.

You can try to aggregate the KPI with SUM like:
SUM(

CASE

    WHEN left("o_celonis_MaterialMasterPlant"."ID", 3) = 'P20'

    AND "o_celonis_MaterialMasterPlant"."PlannedDeliveryTimeDays" IS NOT NULL

    AND "o_celonis_MaterialMasterPlant"."PlannedDeliveryTimeDays" != 0

    THEN "o_celonis_MaterialMasterPlant"."PlannedDeliveryTimeDays"

ELSE 0
END)

This should fix the creating an extra column for the null values issue.

Using PU functions with specified filters instead of CASE WHEN statement is another option.

Hope this helps.

Thank you for the suggestion!
 

However, using SUM will give me 0 instead of NULL, which is not what I am trying to achieve.

Some values are split across two rows. In one row, I have Lead Time and Price for P20 but P18 is null, and in the next row, it's the opposite. I want to combine them into a single row so both P18 and P20 values are shown together — not split or null or 0.
 

Is there a way to achieve this?

Also can you mention your PU function Idea?
 

Appreciate your input!

For PU, you can try something like this for each version,
SUM(PU_SUM(DOMAIN_TABLE( "o_celonis_MaterialMasterPlant"."ObjectID"),"o_celonis_MaterialMasterPlant"."PlannedDeliveryTimeDays",YOUR FILTERS FOR VERSION A HERE))

Do you have any additional column in the table about material master plant ?.

This can be the reason of 2 rows of result in CASE WHEN. It’ll be same in PU too if you have any.

Price and Lead Time are both from MaterialMasterPlant

The easier way is you need to first do this in your backend 

Create a new table something like this

SELECT
    Number,
    MAX(LeadTimeA) AS LeadTimeA,
    MAX(LeadTimeB) AS LeadTimeB,
    MAX(PriceA) AS PriceA,
    MAX(PriceB) AS PriceB
FROM
    your_table_name
GROUP BY
    Number;

Now when you display the results you shold not have a problem