Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

In Clause in QlikView

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

1 Solution

Accepted Solutions
cocosmina
Contributor II

Re: In Clause in QlikView

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

View solution in original post

5 Replies

Re: In Clause in QlikView

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

Capture.PNG

Re: In Clause in QlikView

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

Not applicable

Re: In Clause in QlikView

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

cocosmina
Contributor II

Re: In Clause in QlikView

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

View solution in original post

Not applicable

Re: In Clause in QlikView

Thanks