Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Read about Qlik’s Response to COVID-19 Read the Letter, Join the Group.
Highlighted
Contributor

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

CustomerIdName
1A
2b
3c
4d
5e
6f
7g
8i
CustomerIdService_Visit_Date
101-01-2016
201-01-2018
305-06-2016
110-01-2018
405-07-2017

CustomerIdSales_Visit_Date
101-01-2016
201-01-2018
305-06-2016
510-01-2018
705-07-2017

how to get unvisited customer from sales and service

Total No.of Unvisited Customer :2

Total No.of Visited Customer :6

7 Replies
Highlighted
Valued Contributor II

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

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;

Highlighted
Contributor

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

I need to create in chart not in load

Highlighted
Valued Contributor

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

use a trigger and action as select excluded

Highlighted
Valued Contributor II

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

Use name as Dimension and try the below expression

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

Highlighted
Valued Contributor II

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

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)

Highlighted

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

Hi,

Use indirect Set analysis e() in set analysis.

like

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

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

Regards,

Prashant Sangle

Great dreamer's dreams never fulfilled, they are always transcended.
Highlighted
Honored Contributor

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

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;