Logic help

[ganeshreddy]

Hi All,

I have table as attached, how to get the Opportunity value?. i.e

Opportunity = sum(Exclude the rows from the calculation where 'opp string' includes 'customer' value. So, if anywhere in the pipe separated listed and a single 'customer' appears, the row will be excluded).

Thanks,

Ganesh  

[vishsaggi]

May be try this?

Tab:

LOAD *, IF( Index(String, 'CUSTOMER') > 0, 1, 0) AS CustFlag;

LOAD customer,

     upper([opp string]) as String,

     Sales

FROM

[New Microsoft Excel Workbook.xlsx]

(ooxml, embedded labels, table is Sheet1);

I added couple of values as customer inside your opp string fields you can add and check.

After loading this using Straight table add like

Dim: customer, String

Expr: = Sum({< CustFlag = {0} >} Sales)

[ganeshreddy]

Hi Nagaraju, thanks for your reply.

Here we can not hard code any customer names in logic as we have so many customers present in table and the calculation should apply to every row of the table, However the comparison has to happen in between the strings (customer, opp string) in the logic.

Please suggest on this.

Thanks,

Ganesh

[ganeshreddy]

Hi tresesco

Can you help me on this.

Thanks,

Ganesh

[tresesco]

This?

Sum({<[opp string] -={"*customer*"}>}Sales)

[jonathandienst]

Create a table with customer as dimension and this as the expression:

Sum({<customer = {"$(=index('|' & [opp string] & '|', '|' & customer & '|') = 0)"}>} Sales)

or

Sum(If(index('|' & [opp string] & '|', '|' & customer & '|') = 0, Sales))

The pipes '|' avoid problems if one customer name is also part of another customer name (eg 'gia' and 'gia1' would be distinct)

[jyothish8807]

Hi Ganesh,

Can you please explain your requirement a bit  more clearly ? What do you mean by customer name ?

Or you share a expected output from the file you have provided.

Then it will be easy for us to help you.

Br,

KC

[jyothish8807]

Jonathan's solution should work for you

[vishsaggi]

I did not meant to hardcode any values your excel sheet doesnot have any text customer so for testing purposes i added.