Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
sashimisan77
New 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.

 

1 Solution

Accepted Solutions
Partner
Partner

Re: Autogenerate values for each dimension

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
#OnVacation #BetweenJobs.
Please ekskuse my Norglish and Swenglish typos.
8 Replies
Channa
Valued Contributor III

Re: Autogenerate values for each dimension

sashimisan77
New Contributor III

Re: Autogenerate values for each dimension

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.


Partner
Partner

Re: Autogenerate values for each dimension

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)
#OnVacation #BetweenJobs.
Please ekskuse my Norglish and Swenglish typos.
sashimisan77
New Contributor III

Re: Autogenerate values for each dimension

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

Re: Autogenerate values for each dimension

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.

Partner
Partner

Re: Autogenerate values for each dimension

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
#OnVacation #BetweenJobs.
Please ekskuse my Norglish and Swenglish typos.
sashimisan77
New Contributor III

Re: Autogenerate values for each dimension

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

Re: Autogenerate values for each dimension

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.
#OnVacation #BetweenJobs.
Please ekskuse my Norglish and Swenglish typos.