topic Re: E() or p() in if statement in QlikView

E() or p() in if statement

jduluc12 — Sat, 27 May 2017 16:33:32 GMT

Hi,

I have data set here

date customer value state

01/02/2017 cust1 10 CA

02/03/2017 cust2 20 FL

03/04/2017 cust3 50 NY

04/04/2017 cust4 69 CA

05/04/2017 cust4 69 IL

I want to see if there is any customer who is not from state CA in our system before a certain date. lets say before 03/05/2017.

I can write a dimension like this

if (not match(state, 'CA') and date<'03/05/2017', customer)

it gives the data but i am just wondering if we can E() or P() in this if statement to bring out the same result.

-Jean

Re: E() or p() in if statement

Anonymous — Sat, 27 May 2017 16:56:44 GMT

If you need your calculation row by row you can't use set analysis at all.

E() and P() are only avaible as a set modifier within set analysis. If you need just a preselection you could use something like that:

sum({<

date={"<='03/05/17'"},state-={"CA"}

>} value)

--> no P() or E() for this example...

Re: E() or p() in if statement

Sergey_Shuklin — Sat, 27 May 2017 16:57:49 GMT

Hello, Jean!

You can use a set analysis:
sum({<date={"<03/05/2017"},state=-{'CA'}>}value)

or the same expression for customers count.

Re: E() or p() in if statement

sunny_talwar — Sat, 27 May 2017 16:58:08 GMT

May be like this

Dimension

Customer

Expression

Sum({<state -= {'CA'}, date = {"$(='<' & Date(MakeDate(2017, 5, 3), 'DD/MM/YYYY'))"}>} value)

Re: E() or p() in if statement

hector_munoz — Sat, 27 May 2017 16:58:36 GMT

Hi Jean,

Try the following expression in a calculated field:

=Aggr(MaxString({<customer=P({<state={*}-{CA},date={"<03/05/2017"}>})>} customer), customer)

Regards,
H

Re: E() or p() in if statement

sunny_talwar — Sat, 27 May 2017 16:59:14 GMT

Or may be this

Sum({<Customer = p({<state -= {'CA'}, date = {"$(='<' & Date(MakeDate(2017, 5, 3), 'DD/MM/YYYY'))"}>} Customer)>} value)

Re: E() or p() in if statement

swuehl — Sat, 27 May 2017 17:01:53 GMT

This one used as dimension (e.g. in a list box) returns customer 2 and 3, with a date format set to 'DD/MM/YYYY'

=Aggr(Only({<customer = e({1<state = {'CA'}, date = {"<03/05/2017"}>} ) >} customer), customer)

Note that this is different from your original expression, though could be correct, if I understand your request correctly.