topic Re: Logic help in QlikView

Logic help

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

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

Re: Logic help

vishsaggi — Tue, 07 Nov 2017 03:59:19 GMT

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)

Re: Logic help

ganeshreddy — Tue, 07 Nov 2017 04:15:56 GMT

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

Re: Logic help

ganeshreddy — Tue, 07 Nov 2017 05:15:56 GMT

Hi tresesco

Can you help me on this.

Thanks,

Ganesh

Re: Logic help

tresB — Tue, 07 Nov 2017 05:32:05 GMT

This?

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

Re: Logic help

jonathandienst — Tue, 07 Nov 2017 05:50:32 GMT

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

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

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)

Re: Logic help

jyothish8807 — Tue, 07 Nov 2017 05:55:27 GMT

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,

Re: Logic help

jyothish8807 — Tue, 07 Nov 2017 06:12:25 GMT

Jonathan's solution should work for you

Re: Logic help

vishsaggi — Tue, 07 Nov 2017 14:50:22 GMT

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