Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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?
Hi,
Check this
Aggr(If(event_date <= '15-03-2012' AND status <> 'C', id_doc), id_doc)
Regards,
Jagan.
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.