topic Translate sql query in qlikview expression in QlikView

Translate sql query in qlikview expression

Tue, 10 Apr 2012 16:16:34 GMT

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.

Translate sql query in qlikview expression

nstefaniuk — Tue, 10 Apr 2012 22:02:45 GMT

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;

Translate sql query in qlikview expression

Wed, 11 Apr 2012 05:23:23 GMT

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?

Translate sql query in qlikview expression

jagan — Wed, 11 Apr 2012 06:23:28 GMT

Hi,

Check this

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

Regards,

Jagan.

Re: Translate sql query in qlikview expression

nstefaniuk — Wed, 11 Apr 2012 06:43:40 GMT

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.