How to create fake records without duplicates

[albert_ortiz90]

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!

[albert_ortiz90]

And same for UserB.

Thanks!

[marcus_sommer]

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 · Accepted solution]

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;