topic Re: In Clause in QlikView in QlikView

In Clause in QlikView

Tue, 08 Nov 2016 14:41:34 GMT

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

Re: In Clause in QlikView

sunny_talwar — Tue, 08 Nov 2016 15:10:32 GMT

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

Re: In Clause in QlikView

its_anandrjs — Wed, 09 Nov 2016 06:15:46 GMT

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

Re: In Clause in QlikView

Thu, 10 Nov 2016 08:14:57 GMT

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

Re: In Clause in QlikView

Anonymous — Thu, 10 Nov 2016 08:50:00 GMT

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

Re: In Clause in QlikView

Wed, 16 Nov 2016 09:15:11 GMT

Thanks