topic Re: Display the last 12 dates via Script Load in QlikView

Display the last 12 dates via Script Load

alalko245 — Wed, 10 Feb 2021 19:07:51 GMT

Hi this is my first time posting here so forgive me if this isn't the right spot. Basically I have a QVD file that saves and appends the last day of every month. I have been doing this since 2018 and realized now I do not need to store this much data and I think storing the last 12 (and also the current date as this runs daily) would be fine.

I've been looking on the forum and found a few solutions which only remove SOME of the old data and don't display the full previous 12 months. It would only display 7 or 8 of the previous months using this formula:

Where monthstart([SNAPSHOT DATE]) >= monthstart(addmonths(today(),-12))

I will attach some screenshots of what i prefer

Thanks

Re: Display the last 12 dates via Script Load

alalko245 — Wed, 10 Feb 2021 19:34:03 GMT

So i noticed if i use the script "Where monthstart([SNAPSHOT DATE]) >= monthstart(addmonths(today(),-18))"

It shows the last 12 months but i have no idea. It may be worth noting that while i tried to save the last day of every month some issues occurred and you'll notice that i am missing June 2020 and i missed Dec 2019 but i saved Jan 2nd 2020 to get as close as i could. I also missed Feb. I'm not sure if this is impacting my script, i am very new to this. I just want it to load the last 12 dates (and also todays current date).

Re: Display the last 12 dates via Script Load

Saravanan_Desingh — Thu, 11 Feb 2021 04:03:04 GMT

So, u want the last 12 Dates in the list and not the last 12 months date.

One solution is,

Re: Display the last 12 dates via Script Load

Saravanan_Desingh — Thu, 11 Feb 2021 04:03:34 GMT

Output:

Re: Display the last 12 dates via Script Load

alalko245 — Thu, 11 Feb 2021 12:27:30 GMT

Yes but every month it will add a new date. So technically i have more dates after 9/30/2020 i just made some test data. So i have 10/31, 11/30, 12/31 (2020), and 1/31/2021. At the end of Feb i will have 2/28/2021. I just wanted it to keep the last 12 dates in there as it grows.

Re: Display the last 12 dates via Script Load

Saravanan_Desingh — Thu, 11 Feb 2021 16:16:00 GMT

My code will pull the last 12 dates.

Where RowNo() < 12

Re: Display the last 12 dates via Script Load

alalko245 — Thu, 11 Feb 2021 17:56:27 GMT

I'm not sure how to implement this. I have a qvx that loads in about 100 fields. and then i append to the QVD if the snapshot date is the last of the month. I tried putting your code below in front of my STORE procedure but it didn't work. I am afraid to add too much and i am not familiar with drop.

LOAD *, RowNo() As ID Resident tab1 Where RowNo() < 12 Order By [SNAPSHOT DATE] Desc;

Is it possible to use something like this to get the same result? Where monthstart([SNAPSHOT DATE]) >= monthstart(addmonths(today(),-12))

I know enough to get this implemented but it's not giving me the dates i'd expect

Re: Display the last 12 dates via Script Load

Saravanan_Desingh — Fri, 12 Feb 2021 00:28:32 GMT

tab1: LOAD * INLINE [ SNAPSHOT DATE 6/30/2019 7/31/2019 8/31/2019 9/30/2019 10/31/2019 11/30/2019 12/31/2019 1/2/2020 1/31/2020 3/31/2020 4/30/2020 5/31/2020 7/1/2020 8/31/2020 9/30/2020 2/10/2021 ]; tab2: NoConcatenate LOAD [SNAPSHOT DATE] As LeastDate, RowNo() As ID Resident tab1 Order By [SNAPSHOT DATE] Desc; Right Join(tab2) LOAD 12 As ID AutoGenerate 1; Let vLeastDate=Peek('LeastDate'); Trace **vLeastDate='$(vLeastDate)'; Drop Table tab1, tab2;

The above code will help you to get the Least Date (12th date from the end). Use the below code to filter the data.

Where monthstart([SNAPSHOT DATE]) >= '$(LeastDate)';