topic Re: SCRIPT in QlikView

SCRIPT

upaliwije — Fri, 30 Dec 2016 04:34:26 GMT

DS:

LOAD * FROM ??DEB_SETT.QVD (qvd)

where Date(MonthEnd(Date#([PERIOD] ,'MM-YYYY')),'DD-MMM-YYYY')='31-DEC-2011' ;

my above syntanx does dnot return desired results. pls help

Re: SCRIPT

Anil_Babu_Samineni — Fri, 30 Dec 2016 04:40:00 GMT

What is the starting Date format? I assume PERIOD has MM-YYYY Format?

Try

DS:

LOAD * FROM ../../../DEB_SETT.QVD (qvd)

where MonthEnd(Date(Date#([PERIOD] ,'MM-YYYY'),'DD-MMM-YYYY'))='31-DEC-2011' ;

Re: SCRIPT

tamilarasu — Fri, 30 Dec 2016 04:52:19 GMT

Hello,

Try this.

LOAD * FROM ??DEB_SETT.QVD (qvd)

where Date(MonthEnd(Date#([PERIOD] ,'MM-YYYY')),'DD-MMM-YYYY')='31-Dec-2011' ;

Re: SCRIPT

upaliwije — Fri, 30 Dec 2016 04:56:47 GMT

yes that is the format eg:12-2011

Re: SCRIPT

upaliwije — Fri, 30 Dec 2016 05:17:02 GMT

I tried what you you suggested. It works. But when I change the script in the following way it does not give expected results.

where Date(MonthEnd(Date#([PERIOD] ,'MM-YYYY')),'DD-MMM-YYYY')<='31-Dec-2011' ;

Results

AMOUNT	PERIOD
68682.7	06-2011
69146.34	06-2011
69683.96	09-2011
71938.81	12-2009
72844.85	12-2008
74772.16	04-2008

Strike through records should not return

Re: SCRIPT

tamilarasu — Fri, 30 Dec 2016 05:27:58 GMT

Simply try this,

Where Date(Date#([PERIOD] ,'MM-YYYY'),'DD-MMM-YYYY') <= Date(MakeDate(2011,12,31),'DD-MMM-YYYY') ;

Also note that all the striked dates are less than 31- Dec-2011.

Re: SCRIPT

upaliwije — Fri, 30 Dec 2016 05:43:51 GMT

Thanks a lot

Re: SCRIPT

Anil_Babu_Samineni — Fri, 30 Dec 2016 05:45:13 GMT

Do you have field/date values like 31-Dec-2011

Or else

You may use this, Why do you use MonthEnd?

DS:

LOAD * FROM ../../../DEB_SETT.QVD (qvd)

where Date(Date#([PERIOD] ,'MM-YYYY'),'DD-MMM-YYYY') = Date(MakeDate(31,12,2011)); //This will give only 31-Dec-2011 Date of Data points. Can you describe your intend

OR Finally, Can you describe whole application or share sample data which demonstrates the output

Re: SCRIPT

tamilarasu — Fri, 30 Dec 2016 05:45:56 GMT

No problem. Have a good day .