One Case ID with different cases

Hello,

I try to create an OLAP table where I can see the reasons why an invoice is being rejected. Since an invoice can be rejected by more than one reason, how can i display all the reasons?

When I do a CASE WHEN it takes the first reason it matches. And is it possible to display more than one reason in one column? E.g. Reason a + Reason b in one column?

Thank you

Sasan

Hi Sasan,

You can concatenate the Reason columns with “||”.
If, for example, you want to separate the reasons with a ‘;’ the syntax would look like this:

“<table_name>”."<Reason_a>" || '; ’ || “<table_name>”."<Reason_b>"

Does this answer your question?

Best wishes,
Calandra

Hello Calandra,

thank you for your answer, it helps but only solves part of my problem. The other one is that when I do a CASE WHEN it just goes through all the cases, and when one case applies for a document it is not counted for the other cases it also applies.

Hope this is not to confusing?

Best Wishes,

Sasan

Hi Sasan,

I’m a little confused about what your trying to achieve with the Case When. Can you please explain in more detail what result the Case When is supposed to return and under what conditions?

Best wishes,

Calandra

What I mean is when you do a CASE WHEN it goes from top to bottom. So when i got a document which matches different WHENs in my formula and I would do a count, it would only be counted in the first WHEN it matches and not the other ones it also matches right?

E.G.

CASE WHEN

Reason A
Reason B
Reason C

My document matches Reason A + C.

In a count or in my OLAP Table only Reason A is displayed but not Reason C.

Hey Sasan,

It is still a bit confusing!

To better understand the problem, a definition of where the reasons in your data model are located and how you match the reasons to your document is needed. Especially useful is the information of where the differente reasons are in one table. Are they seperate Columns e.g. “TABLE”.“COLUMN_REASON1” and “TABLE”.“COLUMN_REASON2” or are they in the same Column and just different rows?

Maybe a example of your code would help to analyse the problem better.

Best Regards,

Benedict

Hey Benedict,

sorry for the confusion. What I basically mean is, when I have this code for example:

CASE
WHEN “THRESHOLDS”.“DEV_PRICE” <> 0 AND “THRESHOLDS”.“DEV_QUANTITY” <> 0 THEN ‘Both’
WHEN “THRESHOLDS”.“DEV_PRICE” <> 0 THEN ‘Price’
WHEN “THRESHOLDS”.“DEV_QUANTITY” <> 0 THEN ‘Quantity’
ELSE ‘Others’
END

When I then use this formula: sum(case when KPI(“threshold test”) = ‘Quantity’ then 1 else 0 end)

every document that falls into the precondition of ‘Both’ will not be counted into ‘Quantity’. And I want to write a KPI that counts the ‘Quantity’ even if the document was already counted earlier.

Hope this helps :see_no_evil:

Hi Sasan,
You could use the following:
sum(case when KPI(“threshold test”) in (‘Quantity’,‘Both’) then 1 else 0 end)

Best regards,
Pol