Value of column A should not be the same as value of column B, how?

[#1]

Hi,

what would be the best way to deal with the following:

I have:

customer_id (joe, ann)

product_id (orange, apple, coffee)

store_postnumber (zip/post number where the stores are located)

customer_postnumber (zip/post number where the customers are from)

1) I need to find out the customers that bought products but have different postnumber than the postnumber of the store:

i tryed:

count({$<Customer_postnumber-={'store_postnumber'}>} customer_id) - this doesnt work

count({$<Customer_postnumber=E({<store_postnumber>})}>} customer_id) - this doesnt work either

what is the correct syntax or function for that, anyone can help out?

and then I need to find out customers same as above but the ones that bought only oranges? how would be the set analysis/expression in that case?

thanks in advance for your help.

ATTACHED a light version of an example. I hope it helps

cant send the original one but it is more or less the same structure that the one i Attached and thanks! Message was edited by: romandahl

[jagan]

HI,

Can you attach the sample file that you are working.

Regards,

Jagan.

[#3]

attached.

btw. there is also a date of the purchase made.. (day month year)..... if it might help but i dont see how

[#4]

=count(if(Customer_postnumber<>Store_postnumber,Customer_id))

=count({<Product_id={'orange'}>} if(Customer_postnumber<>Store_postnumber,Customer_id))

[Gysbert_Wassenaar · Accepted solution]

The easiest way is to create a flag field in the script and use that field in the expression:

LOAD *, if(Customer_postnumber=Store_postnumber,1,0) as f_EqualPostnumber   INLINE [

    Customer_id, Customer_postnumber, Product_id, Package_nr, Store_postnumber

    joe, 1000, apple, 1, 9999

.....etc

The expression then becomes:

count({<f_EqualPostnumber={0}>}Customer_id)

See attached example.