topic Re: AND function? in QlikView

AND function?

jim_chan — Wed, 27 Jul 2016 06:07:10 GMT

Hi guys,

I have a script to write.

OK. I have 2 columns Customer ID(001,002) and Change(A,B).

I would like to see records of Customer ID which have change the "change", at below, Customer ID 'A' as made a change from A to B.

Date	Customer ID	Change
01/01/2016	A	A
02/01/2016	A	B
01/01/2016	B	B

So, i created a calculated dimension - if(change = 'A' AND change = 'B',Customer_ID). It is not working,.......

Re: AND function?

Chanty4u — Wed, 27 Jul 2016 06:09:10 GMT

try

if(change = 'A' or change = 'B',Customer_ID)

Re: AND function?

avinashelite — Wed, 27 Jul 2016 06:14:00 GMT

The above condition will yield you the results only when change has been made by both A and B .. I did not get the your exact requirement ..

try like this

if(change<>Customer_ID,Customer_ID)

Re: AND function?

tamilarasu — Wed, 27 Jul 2016 06:16:03 GMT

Hi Jim,

I am not sure what is your exact requirment. You have mentioed that you are using calculated dimension, so try like below,

=Aggr(If(Concat(Change,'|')='A|B',[Customer ID]),[Customer ID])

Re: AND function?

jim_chan — Wed, 27 Jul 2016 06:17:39 GMT

Hello Avin!

I need a script that only show me customer ID that have made changes. , like the red color square box.

Re: AND function?

avinashelite — Wed, 27 Jul 2016 06:25:25 GMT

would be able to share the sample data for the same

Re: AND function?

jim_chan — Wed, 27 Jul 2016 06:35:35 GMT

sure. ! sample qvw as per attached, Avin

Re: AND function?

jim_chan — Wed, 27 Jul 2016 06:35:59 GMT

tried. not working.

Re: AND function?

jim_chan — Wed, 27 Jul 2016 06:36:28 GMT

tried. not the outcome i am looking for

Re: AND function?

anagharao — Wed, 27 Jul 2016 06:46:34 GMT

Hi,

You could group the data by Customer ID and find out the distinct number of change values.

Re: AND function?

kristof_j — Wed, 27 Jul 2016 06:46:36 GMT

and something like

=if(aggr(Concat( DISTINCT Change, '|', Date), [Customer ID]) = 'A|B', [Customer ID] ,)

This will give you customer A.

Re: AND function?

tamilarasu — Wed, 27 Jul 2016 06:50:45 GMT

Have a look at the attachment.

Re: AND function?

jim_chan — Wed, 27 Jul 2016 06:58:21 GMT

Thanks. But i only want a specific pivot table to have such requirements. If i am using the sample that you gave, it would affect the data model.

Re: AND function?

jim_chan — Wed, 27 Jul 2016 07:30:30 GMT

Hello Tamil,

Quite close, thanks! can i max() something else? it suppose to be able to show the day 7(before change), and 8(changed) as well. but if i took out the Day dimension and use the caluclated, then i got the Max Day. which is 8.

Rgds,

jim

Re: AND function?

tamilarasu — Wed, 27 Jul 2016 07:45:34 GMT

Fine. I have attached a qvw file. Please check and let me know.

Re: AND function?

jim_chan — Wed, 27 Jul 2016 07:55:18 GMT

bro. this one worked fine as i requested.

Re: AND function?

tamilarasu — Wed, 27 Jul 2016 08:00:43 GMT

Great Jim. If you got the answer, please close the thread.