load the last day of the month and the last date in progress

[azucna122]

hello i am new to qlik,

I have two tables loaded in the script: Total_Table and Current_Table.

in Total_Table I have the following code:

for i=0 to 1
Let lastmonth = date(addmonths(today(),'$(1)'),'YYYYYMM');
Total_Table
LOAD
ID,
TOTAL_DATE,
KPI_CODE,
DESCP_KPI
FROM [Table_Total.QVD]
(QVD);

What I want is to load in Total_Table the history of the last 13 months (12 months + current month), from the last day of each month.
that is to say, to load from Jan. only the day 31, in Feb. the day 29 and so respectively
and in Current_Table I have the same loop, but in this table I want to load the last current date, that is, March the 11th day.

how can I do it?

[LRuCelver]

If your data is in chronological order and the TOTAL_DATE field formatted as a date, you should be able to use these load statements for your tables (you need to rename the fields or concatenate the tables):

Total_Table:
NoConcatenate Load
    ID,
    TOTAL_DATE,
    KPI_CODE,
    DESCP_KPI
From [Table_Total.qvd] (qvd)
Where TOTAL_DATE = MonthEnd(TOTAL_DATE);

Current_Table:
NoConcatenate Load
	LastValue(ID) as ID,
	LastValue(TOTAL_DATE) as TOTAL_DATE,
	LastValue(KPI_CODE) as KPI_CODE,
	LastValue(DESCP_KPI) as DESCP_KPI
From [Table_Total.qvd] (qvd);

[azucna122]

No, they do not have the same fields, but the same loop:

Total_Table I have the following code:

for i=0 to 1
Let lastmonth = date(addmonths(today(),'$(1)'),'YYYYYMM');
Total_Table:
LOAD
ID,
TOTAL_DATE,
KPI_CODE,
DESCP_KPI
FROM [Table_Total.QVD]
(QVD);
Next;

Current_Table I have the following code:

for i=0 to 1
Let lastmonth = date(addmonths(today(),'$(1)'),'YYYYYMM');
Current_Table:
LOAD
ID_CT,
TOTAL_DATE,
CODE_CT,
DESCP_CT,
COD_TIP_ACT
FROM [Current_Table.QVD]
(QVD);
Next;

Total_Table must have the history of the last day of the month (load monthly closings) and Current_Table the current maximum date.

[LRuCelver]

Please try this code. The Total_Table loads the last day of the months (if it is available) and the current table the last line loaded into the table. Assuming your data is in chronological order, this should be the maximum date.

Total_Table:
NoConcatenate Load
    ID,
    TOTAL_DATE,
    KPI_CODE,
    DESCP_KPI
From [Table_Total.qvd] (qvd)
Where TOTAL_DATE = MonthEnd(TOTAL_DATE);

Current_Table:
NoConcatenate Load
	LastValue(ID_CT) as ID_CT,
	LastValue(TOTAL_DATE) as TOTAL_DATE,
	LastValue(CODE_CT) as CODE_CT,
	LastValue(DESCP_CT) as DESCP_CT,
	LastValue(COD_TIP_ACT) as COD_TIP_ACT
From [Current_Table.qvd] (qvd);

[azucna122]

It doesn't work, they have different fields.
Total_Table must have the history of the last day of the month (loads monthly closings) and Current_Table the current maximum date.

The loop is the same:

Total_Table I have the following code:

for i=0 to 1
Let lastmonth = date(addmonths(today(),'$(1)'),'YYYYYYYMM');
Total_Table:
LOAD
ID,
TOTAL_DATE,
KPI_CODE,
DESCP_KPI
FROM [Table_Total.QVD]
(QVD);
Next;

Current_Table I have the following code:

for i=0 to 1
Let lastmonth = date(addmonths(today(),'$(1)'),'YYYYYYYMM');
Current_table:
LOAD
ID_CT,
TOTAL_DATE,
CODE_CT,
DESCP_CT,
COD_TIP_ACT
FROM [Current_Table.QVD]
(QVD);
Next;