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

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

nagarjuna005 — Sat, 16 Nov 2024 01:02:22 GMT

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!

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

rubenmarin — Wed, 18 Mar 2020 16:55:53 GMT

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

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

nagarjuna005 — Thu, 19 Mar 2020 06:58:28 GMT

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

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

rubenmarin — Thu, 19 Mar 2020 10:07:22 GMT

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);

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

nagarjuna005 — Thu, 19 Mar 2020 10:28:51 GMT

Hi,

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

Thanks,

Naga.

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

rubenmarin — Fri, 20 Mar 2020 08:26:15 GMT

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);

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

nagarjuna005 — Thu, 26 Mar 2020 16:41:10 GMT

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

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

rubenmarin — Thu, 26 Mar 2020 17:57:27 GMT

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'))

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

nagarjuna005 — Fri, 27 Mar 2020 05:03:56 GMT

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!