topic Re: sql equilvanet in QlikView

sql equilvanet

Tue, 16 Sep 2014 05:15:45 GMT

Hi Folks,

I am trying to build set analysis of the below sql equivalent and the results from the sql are different from that of set. Please could someone guide me in the right direction.

SQL

select distinct c_poly_num

from mis_lpm_lr_earnedprem

where n_year=2014

and '30-JUN-2014' between d_start_date and d_end_Date

Set Analysis

Count({<N_YEAR={'2014'},Policy_Start_Date = {'<=30-06-2014'},Policy_End_Date = {'<=30-06-2014'}>} DISTINCT(C_POLY_NUM))

regards

Varghese

Re: sql equilvanet

senpradip007 — Tue, 16 Sep 2014 05:23:16 GMT

As per my understanding you want to count distinct "Policy No" with in a given period. You can try like:

Count({<N_YEAR={'2014'},Date = {'>=$(=yearstart(Max(Date))) <=$(=Max(Date))'}>} DISTINCT(C_POLY_NUM)

Re: sql equilvanet

vikasmahajan — Thu, 08 Oct 2015 04:32:05 GMT

Count({$<N_YEAR={'2014'},[Date]={">=$(=YearStart(max( [Date])))<=$(=max([Date]))"}>}DISTINCT(C_POLY_NUM))

Should give you result.

Vikas

Re: sql equilvanet

Digvijay_Singh — Thu, 08 Oct 2015 04:44:58 GMT

Count({<N_YEAR={2014},d_start_date={"<=$(=Date(makedate(2014,06,30),'Your Format'))"},d_end_Date={">=$(=Date(makedate(2014,06,30),'Your Format'))"}>}DISTINCT(C_POLY_NUM))

You may need to play with date format and may need small tweaking here and there before you get final expected outcome..