Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Translate sql query in qlikview expression

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.

4 Replies
nstefaniuk
Creator III
Creator III

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;

Not applicable
Author

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
MVP
MVP

Hi,

Check this

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

Regards,

Jagan.


nstefaniuk
Creator III
Creator III

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.