topic Re: IF and set analysis in QlikView

IF and set analysis

felcar2013 — Mon, 18 Feb 2013 15:17:13 GMT

somebody knows how this IF-statement would look like in set analysis?

if(x= 'ab' and y='cd', 0, count distinct(z))

thanks for the help

Re: IF and set analysis

martin59 — Mon, 18 Feb 2013 15:22:59 GMT

Hi,

Your expression is wrong.

It can be

if(x='ab' and y='cd', 0, Count(Distinct z))

Count(Distinct if(x='ab' and y='cd', z))

Only the 2nd may be translated in Set Analysis :

Count({<x={ab}, y={cd}>} Distinct z)

Hope that helps you

Martin Favier

Re: IF and set analysis

felcar2013 — Mon, 18 Feb 2013 15:46:12 GMT

hi Martin

thanks. I tried already that way. it works, but i want to set a condition to the intersection of x and y (two dimensions), where the values are "ab" AND "cd".

other values of the dimension are not showed if i use the set analysis, but i want to see everything. Like this:

the dimensions are X, Y and are assigned different values. I want that when X=ab AND Y = cd, then it should show zero, otherwise, it should count the rest and show them in the fields

Header 1	Header 2	Header 3	Header 4
	Y = a	Y=c	Y=cd
X = e	45	12	12
X = u	78	98	74
X= ab	12	14	45

Final result shall look like

Header 1	Header 2	Header 3	Header 4
	Y = a	Y=c	Y=cd
X = e	45	12	12
X = u	78	98	74
X= ab	12	14	0

Re: IF and set analysis

martin59 — Mon, 18 Feb 2013 16:25:16 GMT

Ok I understand,

so it's a little bit more complicated.

Have you tried this (if we consider ID as the Primary Key) ?

Count({<ID=E({<x={ab}, y={cd}>} ID)>} Distinct z)

This expression depends on your datamodel.

Hope that helps you

Martin Favier

Re: IF and set analysis

martin59 — Mon, 18 Feb 2013 16:25:23 GMT

Ok I understand,

so it's a little bit more complicated.

Have you tried this (if we consider ID as the Primary Key) ?

Count({<ID=E({<x={ab}, y={cd}>} ID)>} Distinct z)

This expression depends on your datamodel.

Hope that helps you

Martin Favier

Re: IF and set analysis

martin59 — Mon, 18 Feb 2013 16:25:37 GMT

Ok I understand,

so it's a little bit more complicated.

Have you tried this (if we consider ID as the Primary Key) ?

Count({<ID=E({<x={ab}, y={cd}>} ID)>} Distinct z)

This expression depends on your datamodel.

Hope that helps you

Martin Favier

Re: IF and set analysis

felcar2013 — Mon, 18 Feb 2013 16:52:11 GMT

thanks

i tried, but the result is nothing (the formula shows no errors, but i do not get any number, i thinking i am making a logical error)

my primary key in the data structure is contact_id, which is at the same time the field i am counting

i imagine with this formula, i am counting the total number of customers, excluding the customers which fall into the intersection of segments:

Count({$<contact_id = e({<[Segment_1] ={'NewCustomer'},[Segment_2]={'LostCustomer'}>} contact_id) DISTINCT contact_id)

Segment_1 = X

Segment_2 = Y

and

'NewCustomer' = 'ab'

'LostCustomer' = 'cd'

the contact_id would be ID and Z

what is wrong?

Re: IF and set analysis

martin59 — Mon, 18 Feb 2013 17:05:07 GMT

Are all your contact_id in NewCustomer AND LostCustomer ?

Martin Favier

Re: IF and set analysis

martin59 — Mon, 18 Feb 2013 17:05:15 GMT

Are all your contact_id in NewCustomer AND LostCustomer ?

Martin Favier

Re: IF and set analysis

martin59 — Mon, 18 Feb 2013 17:05:23 GMT

Are all your contact_id in NewCustomer AND LostCustomer ?

Martin Favier

Re: IF and set analysis

felcar2013 — Mon, 18 Feb 2013 17:25:51 GMT

no, they are distributed everywhere. this a customer migration table, so it is important to see how many customers migrated from a segment_1 in a previous period to a segment_2 in the next period. It may be that they stayed in the segment, and this will be reflected in the diagonal. If they migrated, either up or down in the segment, this will be reflected in the other fields.

If there are nulls, this is reflected in the new / lost customers row / column, and finally, i have the NULL / NULL combination, this is, when the customers are located in the intersection NewCustomer AND LostCustomer.

I got this solved, but i made an outer join of a duplicated table, with different names (for period and segment). then included an IF statment in the script to the the "NULL / NULL" combination and flag it. Then in the expression i used set analysis on only one field (the flagged one) and it worked.

the problem is, i have now more than 4 billion records and the performance is bad. Aggregating data previously in SQL, last as well and seems not to be better., so i thought there may be a way with set analysis directly in the chart

hopefully it is clear, thanks for your help

felipe

Re: IF and set analysis

Anonymous — Mon, 18 Feb 2013 17:49:38 GMT

Hi,

Can you please provide test data in excel,what ever formate you are getting.

Regards

Re: IF and set analysis

felcar2013 — Mon, 18 Feb 2013 19:00:19 GMT

the table structure is like this:

table:

LOADcontact_id,
month,
segment;
SQL SELECT *
FROM DATABASE;

i duplicated this table like (previously store as table.qvd):

table1:

LOADcontact_id,
month as month_1,
segment as segment_1
FROM table.qvd (qvd);

table2:

LOADcontact_id,
month as month_2,
segment as segment_2
FROM table.qvd (qvd);

i got an extra "contact" table including only my customers and their countries

I attached the excel sheet with the data structure