topic Re: Need data till before one Year in QlikView

Need data till before one Year

rishikeshtiwari — Tue, 15 Nov 2016 12:47:55 GMT

Hi Guys,

I have a requirement that I need to load data in QVD till before one year. I have a data set having data from Apr-2013 -Nov-2016.

But I need to load the data from Apr-2013 to Nov-2015 (till before one Year). I have attached an excel sheet. In this sheet yellow highlighted coloured data is not required in QVD. Please let me know how I can load this data in script.

Thanks in Advance

Rishi

Re: Need data till before one Year

sunny_talwar — Tue, 15 Nov 2016 12:50:51 GMT

May be like this:

LOAD *

FROM ....

Where Date <= AddYears(Today(), -1);

Re: Need data till before one Year

its_anandrjs — Tue, 15 Nov 2016 12:59:36 GMT

Try this way also

LOAD Date,

Material,

Plant,

Sales

FROM

[Data Till Before One Year.xlsx]

(ooxml, embedded labels, table is DATA)

where Year(Date) <= Year(Today())-1;

Regards

Anand

Re: Need data till before one Year

its_anandrjs — Tue, 15 Nov 2016 13:03:25 GMT

You can try this way also by Addmonths

LOAD Date,

Material,

Plant,

Sales

FROM

[Data Till Before One Year.xlsx]

(ooxml, embedded labels, table is DATA)

//where Year(Date) <= Year(Today())-1;

where Year(Date) <= AddMonths(Today(), -12);

Regards

Anand

Re: Need data till before one Year

sunny_talwar — Tue, 15 Nov 2016 13:06:03 GMT

its.anandrjs‌ why would you compare Year to a date here?

Where Year(Date) <= AddMonths(Today(), -12);

Did you mean to remove Year() function?

Re: Need data till before one Year

its_anandrjs — Tue, 15 Nov 2016 13:10:13 GMT

Thanks Sunny it is a typo mistake it will be Date

This is new code

LOAD Date,

Material,

Plant,

Sales

FROM

[Data Till Before One Year.xlsx]

(ooxml, embedded labels, table is DATA)

//where Year(Date) <= Year(Today())-1;

where Date <= AddMonths(Today(), -12);

Regards

Anand

Re: Need data till before one Year

rishikeshtiwari — Wed, 16 Nov 2016 06:29:40 GMT

Could you please attach the Qvw file.

Re: Need data till before one Year

rishikeshtiwari — Wed, 16 Nov 2016 06:31:59 GMT

Please attach the Qvw file as well. This logic is not working.

Re: Need data till before one Year

rishikeshtiwari — Wed, 16 Nov 2016 06:32:24 GMT

Please attach the Qvw. Logic is not working.

Re: Need data till before one Year

rishikeshtiwari — Wed, 16 Nov 2016 06:36:19 GMT

Date <= AddMonths(Today(), -12) is Working fine. Thank you a lot.

Re: Need data till before one Year

its_anandrjs — Wed, 16 Nov 2016 06:41:22 GMT

Check the attached file for logic as well.

Regards

Anand

Re: Need data till before one Year

its_anandrjs — Wed, 16 Nov 2016 06:42:16 GMT

Thanks You are Welcome

Regards

Anand

Re: Need data till before one Year

sunny_talwar — Wed, 16 Nov 2016 08:56:58 GMT

Rishi -

I am glad that you finally got what you wanted, but I don't see any difference between the two where statements below

Where Date <= AddYears(Today(), -1);

Date <= AddMonths(Today(), -12);

They mean the exact same thing. In both cases you are going back one year. Just two different ways to say the same thing. I would be very curious to see a sample where the second one worked for you, but not the first one