topic Re: not in from two table(Exclude Value from two table) in Report in QlikView

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

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: not in from two table(Exclude Value from two table)

techvarun — Mon, 29 Jan 2018 09:29:18 GMT

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;

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

Anonymous — Mon, 29 Jan 2018 09:44:04 GMT

I need to create in chart not in load

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

arvind1494 — Mon, 29 Jan 2018 09:47:48 GMT

use a trigger and action as select excluded

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

techvarun — Mon, 29 Jan 2018 10:12:25 GMT

Use name as Dimension and try the below expression

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

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

techvarun — Mon, 29 Jan 2018 10:13:05 GMT

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)

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

PrashantSangle — Mon, 29 Jan 2018 10:27:28 GMT

Hi,

Use indirect Set analysis e() in set analysis.

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

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

Regards,

Prashant Sangle

Re: not in from two table(Exclude Value from two table)

shiveshsingh — Mon, 29 Jan 2018 10:40:42 GMT

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;