1 Reply Latest reply: Jun 13, 2011 8:19 AM by STEVEN GIORDANO IMBROLL RSS

    How to interpret something like this in Qlikview Script

    STEVEN GIORDANO IMBROLL

      select count(c.id)

      from customer_dim c

      where c.id in

       

      (select distinct f.customer_dim_id from event_fact f join product_dim p on f.product_dim_id = p.id

      where event_type_dim_id=1 and created_date='19/03/2011' and f.skin_dim_id='1')

       

      and not exists (select * from event_fact f join product_dim p on f.product_dim_id = p.id

      where event_type_dim_id=1 and f.created_date>=to_date('19/03/2011','DD/MM/YYYY') - interval '30 days'

      and f.created_date<'19/03/2011' and f.skin_dim_id='1' and f.customer_dim_id=c.id and f.skin_dim_id=c.skin_dim_id)

       

      and exists (select * from agg_event_extract agg

      where agg.event_type_dim_id=4 and agg.period='lifetime' and agg.skin_dim_id=1 and agg.customer_dim_id=c.id

      and agg.skin_dim_id=c.skin_dim_id and agg.min_created_cet is not null)

       

       

       

      What I have done -

       

      A:

      LOAD DISTINCT

      customer_dim_id as cid_a,

      (date#(created_date,'DD/MM/YYYY')) as cdate_a

      Resident EventFacts WHERE event_type_dim_id= '1'

       

       

      B:

      LOAD DISTINCT

      customer_dim_id as cid_b,

      (date#(created_date,'DD/MM/YYYY')) as cdate_b

      Resident EventFacts WHERE event_type_dim_id= '1' AND (created_date >= to_date('19/03/2011','DD/MM/YYYY') -30 AND created_date <= to_date('19/03/2011','DD/MM/YYYY'));

       

       

      C:

      LOAD

                customer_dim_id as cid_c

      RESIDENT CustomerLifeTimeValue WHERE deposit_lifetime_min_created_cet <> 'Null'  ;

       

       

      MainQuery:

      LOAD Distinct

          Count( customer_dim_id)

      RESIDENT EventFacts WHERE event_type_dim_id and  not exists(cid_b,customer_dim_id) and exists(cid_c,customer_dim_id);

       

      How shall I go about doing the IN(Sub Query) bit and also the 19/03/2011 is a selection from the main dashboard, How can  I make it into a parameter ?

       

      Hopw I was clear enough

      Steve