Discussion Board for collaboration related to QlikView App Development.
I have a Users table with info by year-month.
The problem is that I need to have all the months for all the years, and a value of 0 for this records.
LOAD * Inline[
Username, Year, Month, Value
UserA, 2017, 01, 150
UserA, 2017, 03, 130
UserA, 2017, 04, 120
UserB, 2017, 01, 100
With the previous table what I need is to have something like this:
UserA, 2017, 02, 0
UserA, 2017, 05, 0
UserA, 2017, 06, 0
UserA, 2017, 07, 0
UserA, 2017, 08, 0
UserA, 2017, 09, 0
UserA, 2017, 10, 0
UserA, 2017, 11, 0
UserA, 2017, 12, 0
Thanks for your response.
I solve the problem by this way:
UserInfoStd --> User info table with values at month-year level
Load Date(Max(UserDate),'DD-MM-YYYY') as MaxDate,
Date(Min(UserDate),'DD-MM-YYYY') as MinDate
Let vMax = Num(Date(Peek('MaxDate')));
Let vMin = Num(Date(Peek('MinDate')));
Let vMin_1 = (Date(Peek('MinDate')));
Drop Table Mix_Min;
Load Distinct Date;
Load MonthStart(Date(($(vMin)-1)+Recno())) as Date
Date as UserDate,
Num(Month(Date)) as UserInfoStd_Date.Month,
Year(Date) as UserInfoStd_Date.Year
Drop Table Master;
View solution in original post
And same for UserB.
You could try something like this:
for each vUser in fieldvaluelist('Username')
temp: load '$(vUser)' as Username, 2017 as Year, num(recno(), '00') as Month autogenerate 12;
join(Users) load * resident temp; drop tables temp;
load Username, Year, Month, alt(Value, 0) as Value resident Users;