Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
alalko245
Contributor
Contributor

Display the last 12 dates via Script Load

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

7 Replies
alalko245
Contributor
Contributor
Author

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

saran7de
Master
Master

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;
saran7de
Master
Master

Output:

commQV86.PNG

alalko245
Contributor
Contributor
Author

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.

saran7de
Master
Master

My code will pull the last 12 dates.

Where RowNo() < 12
alalko245
Contributor
Contributor
Author

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

saran7de
Master
Master

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