Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
albert_ortiz90
Contributor II
Contributor II

How to create fake records without duplicates

Hi,

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.

Users:

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, 01, 150

UserA, 2017, 02, 0

UserA, 2017, 03, 130

UserA, 2017, 04, 120

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

Kind Regards!

1 Solution

Accepted Solutions
albert_ortiz90
Contributor II
Contributor II
Author

Thanks for your response.

I solve the problem by this way:

UserInfoStd --> User info table with values at month-year level

Mix_Min:

Load Date(Max(UserDate),'DD-MM-YYYY') as MaxDate,

Date(Min(UserDate),'DD-MM-YYYY') as MinDate

Resident UserInfoStd_aux;


Let vMax = Num(Date(Peek('MaxDate')));

Let vMin = Num(Date(Peek('MinDate')));

Let vMin_1 = (Date(Peek('MinDate')));


Drop Table Mix_Min;



Master:

Load Distinct Date;

Load MonthStart(Date(($(vMin)-1)+Recno())) as Date

Autogenerate($(vMax)-$(vMin))+1;


Join

Load Distinct

UserInfoStd_User

Resident UserInfoStd_aux;


Join(UserInfoStd_aux)

Load

UserInfoStd_User,

Date as UserDate,

Num(Month(Date)) as UserInfoStd_Date.Month,

Year(Date) as UserInfoStd_Date.Year

Resident Master;


Drop Table Master;

View solution in original post

3 Replies
albert_ortiz90
Contributor II
Contributor II
Author

And same for UserB.

Thanks!

marcus_sommer
MVP & Luminary
MVP & Luminary

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;

next

join(Users) load * resident temp; drop tables temp;

load Username, Year, Month, alt(Value, 0) as Value resident Users;

- Marcus

albert_ortiz90
Contributor II
Contributor II
Author

Thanks for your response.

I solve the problem by this way:

UserInfoStd --> User info table with values at month-year level

Mix_Min:

Load Date(Max(UserDate),'DD-MM-YYYY') as MaxDate,

Date(Min(UserDate),'DD-MM-YYYY') as MinDate

Resident UserInfoStd_aux;


Let vMax = Num(Date(Peek('MaxDate')));

Let vMin = Num(Date(Peek('MinDate')));

Let vMin_1 = (Date(Peek('MinDate')));


Drop Table Mix_Min;



Master:

Load Distinct Date;

Load MonthStart(Date(($(vMin)-1)+Recno())) as Date

Autogenerate($(vMax)-$(vMin))+1;


Join

Load Distinct

UserInfoStd_User

Resident UserInfoStd_aux;


Join(UserInfoStd_aux)

Load

UserInfoStd_User,

Date as UserDate,

Num(Month(Date)) as UserInfoStd_Date.Month,

Year(Date) as UserInfoStd_Date.Year

Resident Master;


Drop Table Master;

View solution in original post