Autogenerate values for each dimension

[sashimisan77]

I am struggling with how to use AUTOGENERATE to fill in the gaps within my data model. My data consist of events that occur along with the event_type, hospital, and department. When I create a run sum of events by month, there are no data for some months after filters are applied. This causes a bar or combo chart summarizing events to skip the month in which there were no events for the selection.

Here is some example data. My initial load is actually from a file but I provided an inline load for example purposes.

[EVENTS]:

Load
*
Inline [
Event_Type, Event_Date, Hospital,   Department,
A,  2/4/2019, HOSPITAL A,  SURGICAL
B, 3/3/2019, HOSPITAL B,  ICU
A, 1/10/2019, HOSPITAL C, MEDICAL
B, 3/8/2019, HOSPITAL B, ICU
C, 2/28/2019, HOSPITAL A, OR
C, 1/20/2019, HOSPITAL A, OR
C, 2/15/2019, HOSPITAL A, OR
A, 3/19/2019, HOSPITAL A, ICU
B, 3/18/2019, HOSPITAL B, ORTHO
];

[Events_Summary]:

LOAD

Event_Type,

Event_Date,

Hospital,

Department,

1 as Event_Count

Resident [EVENTS];

DROP TABLE [EVENTS];

I would like to use the load script to autogenerate a value of 0 (zero) Event_Count for each unique combination of year-month, event_type, Hospital and Department. That way there will always be at least a monthly SUM(Event_Count) of 0 regardless of the selection along event_type, hospital or department.

[Channa]

did you try below 

https://community.qlik.com/t5/Qlik-Design-Blog/Autogenerate-Your-Data/ba-p/1465273

[sashimisan77]

Thanks for your response Channa. I did read this post but was unable to understand how this scripting would be applied to my data. I am hoping I can get help actually writing a script that works for my data set.

[Vegar]

Try this code after you ran your code in your post.

For each vEvent_Type in fieldvaluelist('Event_Type')
For each vEvent_Date in fieldvaluelist('Event_Date')
For each vHospital in fieldvaluelist('Hospital')
For each vDepartment in fieldvaluelist('Department')
Concatenate (Events_Summary)
LOAD
'$(vEvent_Type)' as Event_Type,
'$(vEvent_Date)' as Event_Date,
'$(vHospital)' as Hospital,
'$(vDepartment)' as Department,
0 as Event_Count
Autogenerate 1;
Next vEvent_Type
Next vEvent_Date
Next vHospital
Next vDepartment

(Edit: I'm sorry for the bad formatting and indentation of the script sample, but the formatting possibilities are limited when typing on a mobile device)

[sashimisan77]

I get and error if I insert that script and run the data load



Started loading data



EVENTS << 847ba17f-3f02-48b8-8efa-01ce7e367b48

Lines fetched: 9

Events_Summary << EVENTS

Lines fetched: 9

Events_Summary << AUTOGENERATE(1)

Lines fetched: 10



The following error occurred:





The error occurred here:

Next vEvent_Type



Data has not been loaded. Please correct the error and try loading again.

[sashimisan77]

I am attaching a QVF file to help with the troubleshooting. My goal is the get the combo chart bars to show zero regardless of the filter pane selections.

[Vegar · Accepted solution]

Oh, my bad! I got the NEXTs in the wrong order. Try the script below, I've even added logic for excluding zero transactions for combinations that already exists with.

[EVENTS]:
Load
*
Inline [
Event_Type, Event_Date, Hospital,   Department,
A,  2/4/2019, HOSPITAL A,  SURGICAL
B, 3/3/2019, HOSPITAL B,  ICU
A, 1/10/2019, HOSPITAL C, MEDICAL
B, 3/8/2019, HOSPITAL B, ICU
C, 2/28/2019, HOSPITAL A, OR
C, 1/20/2019, HOSPITAL A, OR
C, 2/15/2019, HOSPITAL A, OR
A, 3/19/2019, HOSPITAL A, ICU
B, 3/18/2019, HOSPITAL B, ORTHO
];

[Events_Summary]:
LOAD
	Event_Type,
	Event_Date,
	Hospital,
	Department,
	1 as Event_Count,
	Hash256(Event_Type, Event_Date, Hospital, Department) AS %EventID
Resident [EVENTS];
DROP TABLE [EVENTS];

//Looping throug every possible combination of Type, Date, Hospital and Department
for each vEvent_Type in fieldvaluelist('Event_Type')
	For each vEvent_Date in fieldvaluelist('Event_Date')
		For each vHospital in fieldvaluelist('Hospital')
			For each vDepartment in fieldvaluelist('Department')
			Concatenate (Events_Summary)
			LOAD
			Hash256('$(vEvent_Type)','$(vEvent_Date)' ,	'$(vHospital)', '$(vDepartment)') AS %EventID,
			'$(vEvent_Type)' as Event_Type,
			'$(vEvent_Date)' as Event_Date,
			'$(vHospital)' as Hospital,
			'$(vDepartment)' as Department,
			0 as Event_Count
			Autogenerate 1
			WHERE 
                           //Excluding combinations that already exists in the table.
			   NOT EXISTS(%EventID, Hash256('$(vEvent_Type)','$(vEvent_Date)', '$(vHospital)', '$(vDepartment)'))
			;
			Next vDepartment
		Next vHospital
	Next vEvent_Date
Next vEvent_Type

DROP FIELD %EventID  //You don't need this column in the datamodel

[sashimisan77]

Thanks so much that works well! I look forward to studying the script to understand better how it works. I appreciate your time.

[Vegar]

Please ask if you have trouble understanding parts of it. In short I do the following:

1. The series of FOR EACH ... NEXT allows you to loop through all available field value in a series of fields.
2, I store them into the Even_Summary table, but only if the unique combination of Event_Type, Event_Date, Hospital and Department already exsists. To do that check I temporary create the hash256 generated field.