In Clause in QlikView

[#1]

Hello,

I have a table with the following:

     PARENT_ACCOUNT_NO,

     SUBSCR_NO,

     OFFER_INST_ID,

     OFFER_ID,

     DISPLAY_VALUE,

     ACTIVE_DT,

     INACTIVE_DT,

I want to filter something like this sql:

select SUBSCR_NO from TABLE

where SUBSCR_NO in (select SUBSCR_NO from TABLE where OFFER_ID in (3,5,6))

and OFFER_ID in (7,8,9)

So basically I want subscribers that have both offers. This is pretty easy with sql but in qlikview I can't seem to do it.

BTW I know about match function

Thank you

[sunny_talwar]

The second OFFER_ID looks outside of the inner Select statement? Is this right?

[its_anandrjs]

Try and check did it works for you

select SUBSCR_NO from TABLE

where SUBSCR_NO in (select SUBSCR_NO from TABLE where OFFER_ID in (3,5,6,7,8,9))

Also why this line with and key word

and OFFER_ID in (7,8,9)

Regards

Anand

[#4]

Hello,

The reason it's outside is because I want to show only subscribers which have one of the offers 3,5,6 and 7,8,9

I need the equivalent qlikview script for this sql script

[Anonymous · Accepted solution]

Hi,

I think you whant this:

Table:

LOAD * INLINE [SUBSCR_NO, OFFER_ID

10, 3

10,7

11,3

11,5

12,7

12,10

];

NoConcatenate

Table_FirstOffer:

LOAD distinct SUBSCR_NO

resident Table

where match(OFFER_ID,3,5,6); //Selecting the first Offer

inner join (Table_FirstOffer)

LOAD distinct  SUBSCR_NO

Resident Table

where match(OFFER_ID,7,8,9);//Selecting the second Offer

rename table Table_FirstOffer to Table_SecondOffer;

Best regards,

Cosmina

[#6]

Thanks