topic Re: for loop with dates in QlikView

for loop with dates

Wed, 25 Nov 2020 16:16:04 GMT

hello,

this is my script:

S_PERSONAL_A_Team:

LOAD

date('01/'&RIGHT(KEY_4,7),'DD/MM/YYYY') as MONTH_YEAR, // '01/06/2016'

Team as S_TEAM_NAME,

MAX_PERSONAL_AVG as S_MAX_PERSONAL_AVG

RESIDENT PERSONAL_A_Team;

TEMP_MAX_MIN_DATE:

LOAD MAX(MONTH_YEAR) AS MAX_S_MONTH_YEAR,

MIN(MONTH_YEAR) AS MIN_S_MONTH_YEAR

RESIDENT S_PERSONAL_A_Team;

SET DateFormat='DD/MM/YYYY';

LET vMinTempDate = Peek('MIN_S_MONTH_YEAR',0,'TEMP_MAX_MIN_DATE');

LET vMaxTempDate = Peek('MAX_S_MONTH_YEAR',0,'TEMP_MAX_MIN_DATE');

FOR i= $(vMinTempDate) to $(vMaxTempDate)

let Z= '01'&'/'&right(date($(i)),7); // since the loop running on months i always set it to the first day

let j= date(AddMonths('01'&'/'&right(date($(i)),7),-2),'DD/MM/YYYY'); // variable of two months ago

TEMP_S_PERSONAL_A_Team:

LOAD S_TEAM_NAME&'/'&right(date($(Z)),7) as KEY_4,

S_TEAM_NAME as S_TEAM_NAME_NEW,

$(Z) as new_S_MONTH_YEAR,

// $(j) as two_months_ago,

MAX(S_MAX_PERSONAL_AVG) as maxScore

RESIDENT S_PERSONAL_A_Team

where (S_MONTH_YEAR<=$(Z) and S_MONTH_YEAR>=$(j))

Group by S_TEAM_NAME,$(Z);

NEXT i

///////////////

I want to get a table that for each date (new_S_MONTH_YEAR) I will get the maxSCore from i to j

my problem is that when I executing script 0 line fetched from TEMP_S_PERSONAL_A_Team.

Re: for loop with dates

m_woolf — Thu, 05 Jan 2017 17:35:05 GMT

Try checking the value for vMinTempDate and vMaxTempDate.

These must be numbers, not dates.

Re: for loop with dates

Thu, 05 Jan 2017 20:59:34 GMT

If you try with FirstSortedValue() ?

https://help.qlik.com/en-US/qlikview/12.0/Subsystems/Client/Content/Scripting/AggregationFunctions/firstsorted.htm

Re: for loop with dates

sfatoux72 — Thu, 05 Jan 2017 21:20:11 GMT

‌Hi,

your first LOAD seems to be wrong.

The function Date(...) is wrong because the first parameter of this function need to be a number.

In your case, you need to use Date#(...) where the first paramete is a text.

S_PERSONAL_A_Team:

LOAD

Date#('01/'&RIGHT(KEY_4,7),'DD/MM/YYYY') as MONTH_YEAR, // '01/06/2016'

Team as S_TEAM_NAME,

MAX_PERSONAL_AVG as S_MAX_PERSONAL_AVG

RESIDENT PERSONAL_A_Team;

Re: for loop with dates

Sun, 08 Jan 2017 08:41:42 GMT

You were right, I added Num() and it worked, thanks