Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
t_chetirbok
Creator III
Creator III

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
PrashantSangle

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.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
t_chetirbok
Creator III
Creator III

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)

t_chetirbok
Creator III
Creator III

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

Anonymous
Not applicable
Author

Follow some of the examples here on Indirect Set Analysis:

Set Analysis Wizard for QlikView | qlikblog.at

Not applicable
Author

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!

effinty2112
Master
Master

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
t_chetirbok
Creator III
Creator III

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)

Not applicable
Author

Hi Tatsiana,

it works. Thanks!