topic Re: "Select not in" in Set Analysis in QlikView

"Select not in" in Set Analysis

Fri, 20 Nov 2015 11:30:51 GMT

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

Re: "Select not in" in Set Analysis

PrashantSangle — Fri, 20 Nov 2015 11:38:25 GMT

Hi,

check indirect Set analysis e()

Search on community about p() and e()

It will help you to acheive your requirement.

Regards

Re: "Select not in" in Set Analysis

t_chetirbok — Fri, 20 Nov 2015 13:03:09 GMT

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)

Re: "Select not in" in Set Analysis

t_chetirbok — Fri, 20 Nov 2015 13:04:16 GMT

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

Re: "Select not in" in Set Analysis

Anonymous — Fri, 20 Nov 2015 13:09:57 GMT

Follow some of the examples here on Indirect Set Analysis:

Set Analysis Wizard for QlikView | qlikblog.at

Re: "Select not in" in Set Analysis

Fri, 20 Nov 2015 15:05:41 GMT

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!

Re: "Select not in" in Set Analysis

effinty2112 — Fri, 20 Nov 2015 16:26:08 GMT

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

Re: "Select not in" in Set Analysis

t_chetirbok — Mon, 23 Nov 2015 06:08:35 GMT

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)

Re: "Select not in" in Set Analysis

Mon, 23 Nov 2015 10:52:05 GMT

Hi Tatsiana,

it works. Thanks!