How to selecting every year end date and the maximum date via load script?

[pam1990]

I want the load script to select the records with a year end date and the maximum date.

My field is called period date.

If I were to run the script today, the maximum date would be 4/30/2021 and I want all the records with 12/31.

How do I accomplish that in a load script?

I am thinking I may need to manipulated the yearend function in some way, but I don't know how. Also, I do not want, or need, the timestamp portion for the yearend function.

[pam1990]

I was overthinking this one.

So far I got the yearend date part working by making my where clause include the following:

(Month(ACCOUNTING_DATE) = 12
and Day(ACCOUNTING_DATE) = 31)

Now I have to figure out how to also select the maximum date records.

[mato32188]

Hi @pam1990 ,

I would suggest you to create flag in script editor in table where ACCOUNTING_DATE is presented

Load *,

(floor(MonthEnd(ACCOUNTING_DATE))=floor(ACCOUNTING_DATE))*(-1) as "Is Last Day of Month"

FROM xxx

and then in GUI, select "Is Last Day of Month" with value = 1, it will select all last accounting days of months, create bookmark (named Default Bookmark Selection), go to App Overview, section "Bookmarks", right click on created bookmark and click on Set as default bookmark.

When user opens app, it will automatically make selection in Is Last Day of Month field which means it will select all last days of months in dataset.

Best regards,

m

[pam1990]

Thank You.

Maybe I was not clear?

You see, our table already has the last day of each month.

What I want is all the year ends AND only the prior month end.

So as of today that would be:

4/30/2021

12/31/2020

12/31/2019

12/31/2018, etc.

I've already solved for the 12/31 records. I now need to add the prior month record.

[mato32188]

Right, now it is a bit more better.

Try to use 

(((floor(YearEnd(ACCOUNTING_DATE))=floor(ACCOUNTING_DATE))) OR ((floor(MonthEnd(AddMonths(Today(),-1)))=floor(ACCOUNTING_DATE))))*(-1) as "Is Last Date"

BR

m

[pam1990]

Thank You

I am new to Qlik. Why is the bookmark the suggested solution?

[mato32188]

You are welcome.

There is one more option to tell Qlik, how to pre-select values in fields. You can use Action in Sheet properties and trigger selection in field. In case you have more sheets in application, you have to setup such action for every single sheet therefore bookmark is much easier way to get the result.

m

[pam1990 · Accepted solution]

I received assistance from someone else on this, which worked better for what I needed. 

In the load script with a variable, then the variable was used later in the load script.

MAX_ACCOUNTING_DATE:
LOAD
MAX(DATE(ACCOUNTING_DATE)) AS ACCOUNTING_DATE
FROM [lib://SenseData/NYL_REG_DATAMART.qvd]
(qvd)
;

LET vMaxAccountingDate = Peek('ACCOUNTING_DATE',0,'MAX_ACCOUNTING_DATE')
;

DROP TABLE MAX_ACCOUNTING_DATE;