Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Talk to Experts, a LIVE Q&A Webinar. Bring your Qlik Sense Business questions on Aug. 4th. Register
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

Data update in Load script

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

1 Solution

Accepted Solutions
Highlighted
Partner
Partner

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
;

View solution in original post

7 Replies
Highlighted
Partner
Partner

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
;

View solution in original post

Highlighted
Contributor
Contributor

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

 

Highlighted
Partner
Partner

Hi Chris,

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

Whitey.

Highlighted
Contributor
Contributor

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

Highlighted
Partner
Partner

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

Highlighted
Contributor
Contributor

Hey Whitey,

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

Cheers
Chris

 
Highlighted
Partner
Partner

Chris,

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