not in from two table(Exclude Value from two table) in Report

[Anonymous]

CustomerId	Name
1	A
2	b
3	c
4	d
5	e
6	f
7	g
8	i

CustomerId	Service_Visit_Date
1	01-01-2016
2	01-01-2018
3	05-06-2016
1	10-01-2018
4	05-07-2017

CustomerId	Sales_Visit_Date
1	01-01-2016
2	01-01-2018
3	05-06-2016
5	10-01-2018
7	05-07-2017

how to get unvisited customer from sales and service

Total No.of Unvisited Customer :2

Total No.of Visited Customer :6

[techvarun]

123:
LOAD * INLINE [
    CustomerId, Name
    1, A
    2, b
    3, c
    4, d
    5, e
    6, f
    7, g
    8, i
];


124:
LOAD *, CustomerId as TestCust INLINE [
    CustomerId,Service_Visit_Date
    1, 01-01-2016
    2, 01-01-2018
    3, 05-06-2016
    1, 10-01-2018
    4, 05-07-2017
];


125:
LOAD *,CustomerId as TestCust INLINE [
    CustomerId,Sales_Visit_Date
    1, 01-01-2016
    2, 01-01-2018
    3, 05-06-2016
    5, 10-01-2018
    7, 05-07-2017
];


NoConcatenate
LOAD * Resident 123 Where Not Exists(TestCust, CustomerId);


DROP TABLE 123,124,125;

[Anonymous]

I need to create in chart not in load

[arvind1494]

use a trigger and action as select excluded

[techvarun]

Use name as Dimension and try the below expression

If(CustomerId+ Count(Sales_Visit_Date)*10+Count(Service_Visit_Date)*10 <10,CustomerId)

[techvarun]

You can also try the expression vice versa

CustomerId as Dimention

Expression : If(CustomerId+ Count(Sales_Visit_Date)*10+Count(Service_Visit_Date)*10 <10,Name)

[PrashantSangle]

Hi,

Use indirect Set analysis e() in set analysis.

like

sum({<Id=e(Id1)>}Sales)

search for p() & e() in communtiy.

Regards,

Prashant Sangle

[shiveshsingh]

Hi Try this

A:
load * inline [CustomerId,Name
1, A
2, b
3 ,c
4 ,d
5, e
6 ,f
7 ,g
8 ,i ];

B:load CustomerId as C_ID,Service_Visit_Date inline [CustomerId,Service_Visit_Date

1, 01-01-2016
2, 01-01-2018
3, 05-06-2016
1,10-01-2018
4, 05-07-2017
];
Concatenate
load CustomerId as C_ID,Sales_Visit_Date inline [CustomerId,Sales_Visit_Date
1, 01-01-2016
2 ,01-01-2018
3, 05-06-2016
5, 10-01-2018
7, 05-07-2017];

NoConcatenate
😧
load count(DISTINCT(C_ID)) as TOT_VISIT
Resident B;


NoConcatenate
E:

load *
Resident A
where not Exists(C_ID,CustomerId);
NoConcatenate
F:
load count(CustomerId) as Unvisited_Count
resident E;
drop table A,B,E;