topic Autogenerate using two source tables in QlikView

Autogenerate using two source tables

joffwilliams — Fri, 23 Sep 2016 02:46:55 GMT

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'

];

Re: Autogenerate using two source tables

Anil_Babu_Samineni — Fri, 23 Sep 2016 05:46:31 GMT

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:

Re: Autogenerate using two source tables

tresB — Fri, 23 Sep 2016 06:10:01 GMT

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;

Re: Autogenerate using two source tables

joffwilliams — Thu, 20 Oct 2016 00:49:03 GMT

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