how to get column values within the date range in qlikview script

[nagarjuna005]

Hi Team,

I have the below requirement,

GroupNames,Startdate and Enddate  three fields are there using those we want to restrict groupnames field values using startdate and enddate 

exampl:

GroupNumber   StartDate   EndDate
CBP001   2019/01/01   2019/04/31
CC1000   2019/02/01   2019/05/31
FDA200   2019/03/01   2019/08/30
FDAW02  2019/06/01   2019/06/31
FDAW03  2019/05/01   2019/07/31
FDA300  2019/09/01   2019/12/31
FDAN02  2019/10/01   2019/11/30
MTSN01  2018/06/01  2018/07/30
NYAC1     2018/03/01   2018/06/30

Based on my report date range automatically group names should be picked by matching start date or end date.

Ex: If user want the report from 2019/06/01 to 2019/07/31 then these below groups numbers should be picked by qlikview script.

FDAW02 

FDAW03 

NYAC1 

MTSN01 

thanks for your help!

[rubenmarin · Accepted solution]

Hi, If you have 2 variables with date range selected by the user you can use those variable to filter the data in a where clause. BTW are you sure it needes to match the start and end date or just that the group should be active in at least part of the date range selected by user?

LOAD GroupNumber  Where StartDate<=$(vEndDate) and EndDate>=$(vStartDate);

Then, if needed, you can use exists to only load those groups data:

LOAD GroupNumber, otherFields where exists(GroupNumber);

Another BTW: I don't know why it should pick MTSN01 or NYAC1 as the end date is for 2018 and user is filtering dates on 2019

[nagarjuna005]

Hi Ruben,

Thank you its working and one more thing is if i want to get only active groups within that date range how to do it.

thanks for your help

[rubenmarin]

Hi, If you want groups actives in all the selected range you only need to change the where clause:

LOAD GroupNumber Where StartDate<=$(vStartDate) and EndDate>=$(vEndDate);

[nagarjuna005]

Hi,

I have changed  in where clause to get only active groups within that date range but getting zero records.

Thanks,

Naga.

[rubenmarin]

With 01/06/2019 as start date and 21/07/2019 as end date FDAW03 group data should be taken. Has this group any data?

Maybe you need to enclse dates between simple quotes:

LOAD GroupNumber Where StartDate<='$(vStartDate)' and EndDate>='$(vEndDate)';

Or use variables that uses  dates in number format, to avoid format issues:

LET vStartDateNum=Num(Date('$(vStartDate)'));
LET vEbdDateNum=Num(Date('$(vEndDate)'));

LOAD GroupNumber Where StartDate<=$(vStartDateNum) and EndDate>=$(vEndDateNum);

[nagarjuna005]

Hi Ruben,

My requirement has changed please look into my below requirement,

CompanyNumber,LOB,GroupNumber,Plan ID,Startdate and Enddate  six fields are there using those i want to restrict groupnames field values NYAC1 and MTSN01. 

what i mean is i don't want to see those two inactive groups in dashboard.

exampl:

Company  LOB    GroupNames Plan ID  Startdate      Enddate

30                ISN       NYAC1              035         01012008      12312018

30               MTS      MTSN01           036         01012008      12312018

30               CBP     CBP001             027         01012008      99999999

30                             IBPS65              019         01012008     99999999

30           LIP           LIP65                  021        01012008     99999999

30           MCR       MCR65                001        01012008     99999999

34          CCC         CC1000               034        01012008     99999999

so using above table i want to restrict these two NYAC1 and MTSN01 Inactive groups.

Ex: If user want the report from 2018/12/01 to 2019/07/31 then these below groups numbers should not picked by qlikview script.

NYAC1 and MTSN01

and also LOB is blank in one row we should accommodated that as well  so how to write logic for this.

  could you please provide solution for this thanks!!

[rubenmarin]

Hi, this is the same requirement as previous: get GROUPNAMES actives in all the range, what has changed?

Maybe only beacuse of the format dates? you can convert those numebr to dates using Date#(): Date(Date#(Startdate,'MMDDYYYY'))

[nagarjuna005]

Hi Ruben,

now as per the requirement i want to remove inactive groups as mentioned in my previous post.

i din't want to get those two inactive groups into dashboard.

Thanks!