Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sashimisan77
Contributor III
Contributor III

Autogenerate values for each dimension

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.

 

Labels (3)
1 Solution

Accepted Solutions
Vegar
MVP
MVP

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

View solution in original post

8 Replies
Channa
Specialist III
Specialist III

sashimisan77
Contributor III
Contributor III
Author

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
MVP
MVP

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
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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
MVP
MVP

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
Contributor III
Contributor III
Author

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

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.