Autogenerate using two source tables

[joffwilliams]

Greetings,

I'm attempting to build out a QVW as a sort of "demo" dashboard with some randomly generated numbers applied to actual meaningful values.

I have the following tables created:

• Departments table with an inline load of department names
• Dates table with an autogenerated series of dates from 2015-01-01 to today()

I now need to create a "Consumption" table which should create rand() values for every deparrtment and every date.

I figured out how to use peek() to pull in values from one table, but cannot figure out how to iterate through two tables and generate random values for each combination. Any help would be appreciated!

Current script:

Let vMinDate = Num(MakeDate(2015, 1, 1));

Let vMaxDate = Num(Today());

Dates:

LOAD

  DATE($(vMinDate) + IterNo() - 1) AS [Date],

  DAY($(vMinDate) + IterNo() - 1) AS [Day],

  MONTH(($(vMinDate) + IterNo() - 1)) AS [Month],

  YEAR(($(vMinDate) + IterNo() - 1)) AS [Year]

AutoGenerate 1

WHILE $(vMinDate) + IterNo() - 1 <= $(vMaxDate);

Departments:

LOAD * INLINE

  [

  'Department Name'

  'Information Services'

  'Accounting'

  'Donor Services'

  'Marketing'

  'Supply'

  'Resource Development'

  'Marine Operations'

  'Executive'

  'Chaplaincy'

  'Programs'

  'Staff Development'

  'Human Resources'

  'Operations'

  ];

[Anil_Babu_Samineni]

Have you tried another load

Here, your can create master calendar if required

Load mindate + rowno() - 1 as mindate

While mindate + rowno() -1 <= today();

Load makedate(2015) mindate,

Today() as maxdate

Autogenerate 1:

[tresesco · Accepted solution]

Try something like:

Let vMinDate = Num(MakeDate(2015, 1, 1));

Let vMaxDate = Num(Today());

Dates:

LOAD

  DATE($(vMinDate) + IterNo() - 1) AS [Date],

  DAY($(vMinDate) + IterNo() - 1) AS [Day],

  MONTH(($(vMinDate) + IterNo() - 1)) AS [Month],

  YEAR(($(vMinDate) + IterNo() - 1)) AS [Year]

AutoGenerate 1

WHILE $(vMinDate) + IterNo() - 1 <= $(vMaxDate);

JOIN

Departments:

LOAD * INLINE

  [

  'Department Name'

  'Information Services'

  'Accounting'

  'Donor Services'

  'Marketing'

  'Supply'

  'Resource Development'

  'Marine Operations'

  'Executive'

  'Chaplaincy'

  'Programs'

  'Staff Development'

  'Human Resources'

  'Operations'

  ];

Final:

Load

  *,

  Ceil(Rand()*1000*RowNo()) as Data

  Resident Dates;

  Drop Table Dates;

[joffwilliams]

This is simple, elegant and did exactly what I needed. Thanks.