Translate sql query in qlikview expression

[#1]

Hi, I need translate this SQL query in Qlikview expression:

select distinct id_doc from table1

where event_date <= '15-03-2012'

and id_doc not in (select distinct id_doc from event_date <= '15-03-2012' and status = 'C')

Can anyone help me?

Thanks.

[nstefaniuk]

Hi.

This one should work.

INVALID_VALUES:

Load

distinct

id_doc As  Invalid_id_doc

resident event_date

where event_date <= MakeDate(2012,3,15) and status = 'C';

FINAL:

NoConcatenate Load

distinct

id_doc

resident table1

where

event_date <= MakeDate(2012,3,15)

and not exists(Invalid_id_doc, id_doc)

;

drop table INVALID_VALUES;

[#3]

Hi,

thanks for the reply, however, this solution can be applied during data loading. it is possible instead to have a solution to be used in an expression?    

[jagan]

Hi,

Check this

Aggr(If(event_date <= '15-03-2012' AND status <> 'C', id_doc), id_doc)

Regards,

Jagan.

[nstefaniuk]

In this case, how is your model please?

If you have 2 tables with id_doc and event_date, they should be linked. Else you can use an orphan table and function P() like in the example attached.