IF and set analysis

[felcar2013]

hi

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

[felcar2013]

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

[Anonymous]

Hi,

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

Regards

h

[felcar2013]

hi

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