Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

stevegimbrollmt
Contributor II

How to interpret something like this in Qlikview Script

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

1 Reply
stevegimbrollmt
Contributor II

How to interpret something like this in Qlikview Script

Anyone has an idea on this? not sure If it is well explained, thanks!

Community Browser