Display the last 12 dates via Script Load

[alalko245]

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

[alalko245]

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

[Saravanan_Desingh]

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

One solution is,

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:
LOAD *, RowNo() As ID
Resident tab1
Where RowNo() < 12
Order By [SNAPSHOT DATE] Desc;

Drop Table tab1;

[Saravanan_Desingh]

Output:

[alalko245]

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.

[Saravanan_Desingh]

My code will pull the last 12 dates.

Where RowNo() < 12

[alalko245]

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

[Saravanan_Desingh]

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