Data update in Load script

[fall_chris]

Hi.
I have been trying for a while now but still can't come up with a solution.
I have this set of data from my excel sheet I load into Qlik:
Isaac __2019/06/1 __amount a
Isaac__2020/02/1__amount b
Imani __2020/03/1 __amount c

Now I'd like to have the data turned into this:
Isaac __2019/06/1 __amount a
Isaac__2019/07/1__amount a
Isaac__2019/08/1__amount a
[....]
Isaac_2020/01/1_amount a
Isaac_2020/02/1_amount b
[...]
Isaac__2020/06/1__amount b
Imani__2020/03/1__amount c
Imani __2020/04/1 __amount c
Imani __2020/05/1 __amount c
Imani __2020/06/1 __amount c

As you can see, I want one user to have a line for each month (till the beginning of the current month), with the right amount attached.
But the actual result I get is as follows:

Isaac_2019/06/1_amount a
Isaac_2019/06/1_amount b
Isaac_2019/07/1_amount a
Isaac_2019/07/1_amount b
[...]
Isaac_2020/06/1_amount a
Isaac_2020/06/1_amount b
Imani__2020/03/1__amount c
Imani __2020/04/1 __amount c
Imani __2020/05/1 __amount c
Imani __2020/06/1 __amount c

This is what the script I wrote:
-------------Input table

Spoiler

[input]:
LOAD
username,
payDay,
amount
FROM [lib://AttachedFiles/file.csv]
(txt, utf8, embedded labels, delimiter is ',', msq);

-----------------and a table called DATELIST, containing the 1st day of every month since June 2019

Spoiler

Let StartDate=date('2019/06/01');
Let EndDate=MonthStart(Today());
[dateList]:
LOAD * INLINE [
dummy
4
];
Do while EndDate >= StartDate
LOAD date('$(StartDate)') as [day]
Resident dateList;
Let StartDate=MonthStart('$(StartDate)', 1);
Loop
DROP Table dateList;
Rename Table [dateList-1] to [dateList];

Then perform a Join of the two tables

Spoiler

Left join(input)
 Load
 day
 RESIDENT dateList;

Finally get the output

Spoiler

[output]:
LOAD username,
day AS `payDay`,
Date(monthend(day)) AS `End_fix`,
amount
RESIDENT input
where [payDay] <= day AND [End_fix] >= day;
DROP TABLE [input];

I've been loosing hair. Does anyone see what's wrong with this?
Thanks for reading.

Cheers,

Chris

[whiteymcaces · Accepted solution]

Input:
Load * InLine [
username, payDay, amount
Isaac, 01/06/2019, 1000
Isaac, 01/02/2020, 1100
Imani, 01/03/2020, 1200
];

Data:
Load
username,
payDay,
If(username <> Previous(username),
MonthEnd(Date(Today())),
MonthEnd(AddMonths(Previous(payDay), -1))) as endDay,
amount
Resident Input
Order By username Asc, payDay Desc
;

Drop Table Input;

DateTemp:
Load
Date(Min(payDay)) as MinDate,
Date(Max(endDay)) as MaxDate
Resident Data
;

Let vMinDate = Num(Peek('MinDate', 0, 'DateTemp'));
Let vMaxDate = Num(Peek('MaxDate', 0, 'DateTemp'));

DateField:
Load
Date($(vMinDate) + RowNo() - 1) as TempDate
AutoGenerate
$(vMaxDate) - $(vMinDate) + 1;

TempCalendar:
Load Distinct
Date(MonthStart(TempDate)) as MonthStart
Resident DateField
Order By TempDate Asc
;

Drop Table DateField, DateTemp;

TempIntMatch:
IntervalMatch(MonthStart)
Load Distinct
payDay,
endDay
Resident Data
;

Inner Join (Data)
Load
*
Resident TempIntMatch
;

Output:
Load
username,
MonthStart as payDay,
amount
Resident Data
;

Drop Table TempIntMatch, TempCalendar, Data
;

[fall_chris]

Thanks for taking the time @whiteymcaces. 
The script is stopped by this error:
Autogenerate: generate count is negative: DateField: Load Date( + RowNo() - 1) as TempDate AutoGenerate - + 1

right here

Spoiler

DateField:
Load
Date($(vMinDate) + RowNo() - 1) as TempDate
AutoGenerate
$(vMaxDate) - $(vMinDate) + 1;

I fear vMinDate is not being populated and if not, there might be reason to believe that vMaxDate isn't either.

Cheers,

Chris

[whiteymcaces]

Hi Chris,

Show me your script that assigns values to vMaxDate and vMinDate.

Whitey.

[fall_chris]

Right here I believe:

DateTemp:
Load
Date(Min(pay_day)) as MinDate,
Date(Max(pay_day)) as MaxDate
Resident Data;

Let vMinDate = Num(Peek('MinDate', 0, 'DateTemp'));
Let vMaxDate = Num(Peek('MaxDate', 0, 'DateTemp'));

Just paid better attention to the debugging. the exe stops at the red line. In that case, $(vMaxDate) - $(vMinDate) delivers a negative result?

Spoiler

DateField:
Load
Date($(vMinDate) + RowNo() - 1) as TempDate
AutoGenerate
$(vMaxDate) - $(vMinDate) + 1;

Cheers,

Chris

[whiteymcaces]

Can you please add your entire script so I can check?

[fall_chris]

Hey Whitey,

Sorry I did not respond earlier. Finally made it. Your help was precious.
Thanks for your time

Cheers
Chris

[whiteymcaces]

Chris,

Glad to help. Please mark my initial response as the solution.