Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
Not applicable

"Select not in" in Set Analysis

Hi all,

I have a problem with using the logic of "Select not in" in set analysis.To be precise I have two table:

TAB1

CODEDATEVOLUME

1

1/1/201510
11/2/201520
21/2/201530
31/3/201540
31/4/201550
41/5/201560

TAB2

CODEDATE_RIF
11/5/2015
21/4/2015
31/3/2015
31/4/2015
4

1/3/2015

I want to create a straight table that will be contain all the records of TAB1, in a certain time range, that are not present in TAB2 in the same time range.

For example: If I select the time range from 1/2/2015 to 1/3/2015, the records to be considered in the two tables are those highlighted in red, and the final table to obtain (with only the records that are in TAB1 but no in TAB2) is the follow:

CODEDATEVOLUME
11/2/201520
21/2/201530


I know that to be considered only records in a certain time range I need to use the follow expression "sum({<DATE={'>=$(vStart)<=$(vEnd)'}>} VOLUME) where vStart and vEnd are two variables that contain the values 1/2/2015 and 1/3/2015.

But how can I consider only the records that are in TAB1 and not in TAB2?

Someone can help me?

Thanks

1 Solution

Accepted Solutions
Valued Contributor

Re: "Select not in" in Set Analysis

You should write all conditions betweenn { } with comma.

Your expression look like:

sum({<

ID=e({<ID=p(ID2)>} ID) , DATE={'>=$(vStart)<=$(vEnd)'} , DATE_RIF={'>=$(vStart)<=$(vEnd)'}

>} VOLUME)

View solution in original post

8 Replies
Highlighted

Re: "Select not in" in Set Analysis

Hi,

check indirect Set analysis e()

Search on community about p() and e()

It will help you to acheive your requirement.

Regards

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

Re: "Select not in" in Set Analysis

Hello

I generate new fields in both table like AutoNumber(CODE& DATE) as ID and AutoNumber(CODE& DATE_RIF) as ID2.

Your expression "sum({<DATE={'>=$(vStart)<=$(vEnd)'}>} VOLUME) must be modified like this

sum({<

ID=e({<ID=p(ID2)>} ID) , DATE={'>=$(vStart)<=$(vEnd)'}

>} VOLUME)

Highlighted
Valued Contributor

Re: "Select not in" in Set Analysis

and this article will be helpfull P() &amp; E()

Highlighted
Contributor

Re: "Select not in" in Set Analysis

Follow some of the examples here on Indirect Set Analysis:

Set Analysis Wizard for QlikView | qlikblog.at

Highlighted
Not applicable

Re: "Select not in" in Set Analysis

Hi Tatsiana,

thanks for your answer, it is very helpful.

I have to add the condition on the same time range on the field DATA_RIF too "DATE_RIF={'>=$(vStart)<=$(vEnd)'}".

Where I have to add this condition in your expression?

Thanks!

Highlighted
Honored Contributor

Re: "Select not in" in Set Analysis

Try this:

Add an autonumber field to each table that gives a unique number for each combo of date and code but give each of the two autonumber fields a different fieldname

Tab1:

LOAD

*,

AutoNumber(CODE & '|' & DATE,'CodeDateKey') as CodeDateKeyID;

LOAD * INLINE [

    CODE, DATE, VOLUME

    1, 1/1/2015, 10

    1, 1/2/2015, 20

    2, 1/2/2015, 30

    3, 1/3/2015, 40

    3, 1/4/2015, 50

    4, 1/5/2015, 60

];

Tab2:

LOAD

*,

AutoNumber(CODE & '|' & DATE_RIF,'CodeDateKey') as CodeDateRifKeyID;

LOAD * INLINE [

    CODE, DATE_RIF

    1, 1/5/2015

    2, 1/4/2015

    3, 1/3/2015

    3, 1/4/2015

    4, 1/3/2015

];

Make a straight table with code and date as dimensions and with this as expression:

sum({$<CodeDateRifKeyID = E({<CodeDateKeyID = {$(=Concat(CodeDateRifKeyID,','))}>})>}VOLUME)

and you get this:

CODE DATE VOLUME
120
11/1/201510
11/2/201520
21/2/201530
41/5/201560

Select 1/2/2015 then

CODE DATE VOLUME
50
11/2/201520
21/2/201530
Valued Contributor

Re: "Select not in" in Set Analysis

You should write all conditions betweenn { } with comma.

Your expression look like:

sum({<

ID=e({<ID=p(ID2)>} ID) , DATE={'>=$(vStart)<=$(vEnd)'} , DATE_RIF={'>=$(vStart)<=$(vEnd)'}

>} VOLUME)

View solution in original post

Highlighted
Not applicable

Re: "Select not in" in Set Analysis

Hi Tatsiana,

it works. Thanks!