"Select not in" in Set Analysis

[#1]

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

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

CODE	DATE_RIF
1	1/5/2015
2	1/4/2015
3	1/3/2015
3	1/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:

CODE	DATE	VOLUME
1	1/2/2015	20
2	1/2/2015	30

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

[PrashantSangle]

Hi,

check indirect Set analysis e()

Search on community about p() and e()

It will help you to acheive your requirement.

Regards

[t_chetirbok]

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]

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

[Anonymous]

Follow some of the examples here on Indirect Set Analysis:

Set Analysis Wizard for QlikView | qlikblog.at

[#6]

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]

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
1	1/1/2015	10
1	1/2/2015	20
2	1/2/2015	30
4	1/5/2015	60

Select 1/2/2015 then

CODE	DATE	VOLUME
		50
1	1/2/2015	20
2	1/2/2015	30

[t_chetirbok · Accepted solution]

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)

[#9]

Hi Tatsiana,

it works. Thanks!