Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a series of identical excel worksheets that I'm loading into Qlik Sense.
Each spreadsheet has rows consisting of patient identifiers, column headers listing types of services being delivered. I'm reducing the details to a single service type for this example...
For each location/date/customer, the service types are broken into four subgroups representing the four measures on this abstract matrix:
Weekday | Weekend | |
---|---|---|
Hours of Services Accepted | x | x |
Hours of Services Refused | x | x |
Each of the Excel tables flattens the matrix into distinct rows:
Location | Date | Customer ID | Service A Weekend Hours Provided | Service A Weekend Hours Refused | Service A Weekday Hours Provided | Service A Weekday Hours Refused | ... | ||
---|---|---|---|---|---|---|---|---|---|
north | jan | 001 | .5 | 1 | 1 | ||||
west | jan | 002 | .5 | 1 | |||||
east | feb | 003 | .5 | 3 | 1 | ||||
west | feb | 002 | 1 | ||||||
... |
I want to load this in such a way that I can select for weekday/weekend hours and/or select for provided/refused hours.
Somehow I need to say that certain columns belong to the "weekend" dimension, certain columns belong to the "provided" dimension, and certain columns belong to both "weekend" and "provided" dimensions. (and retain the dimensions of location, date, customer ID)
I imagine there's a simple loading function that I'm missing, but fear i'll have to transform the data in stages. If anyone can point me in the right direction, I'd appreciate it.
hi
this script using cross table
creates 3 new fields : service type values A,B,C
day type values: Weekend,Weekday
hours type values : Provided, refused
now you can build which ever chart you need
attach is an example
[_temp_fe65a0ad-622a-e723-a2f6-db1d9088]:
CROSSTABLE ([Sheet1.Attribute field],[Sheet1.Data field],3)
LOAD
[cation],
[Date],
[Customer ID],
[Service A Weekend Hours Provided],
[Service A Weekend Hours Refused],
[Service A Weekday Hours Provided],
[Service A Weekday Hours Refused]
FROM [lib://AttachedFiles/community.xlsx]
(ooxml, embedded labels, table is Sheet1);
[Sheet1]:
NOCONCATENATE LOAD
[cation],
[Date],
[Customer ID],
[Sheet1.Attribute field],
[Sheet1.Data field],
if(index([Sheet1.Attribute field],'Weekend')>0,'Weekend','Weekday') AS [day type],
if(index([Sheet1.Attribute field],'Provided')>0,'Provided',
'Refused') AS [hours type],
mid([Sheet1.Attribute field],9,1) AS [service type]
RESIDENT [_temp_fe65a0ad-622a-e723-a2f6-db1d9088];
DROP TABLE [_temp_fe65a0ad-622a-e723-a2f6-db1d9088];
Maybe the Crosstable load can help you:
CrossTable(Service, Hours, 3)
LOAD Location,
Date,
[Customer ID],
[Service A Weekend Hours Provided],
[Service A Weekend Hours Refused],
[Service A Weekday Hours Provided],
[Service A Weekday Hours Refused]
FROM
[https://community.qlik.com/thread/311783]
(html, codepage is 1252, embedded labels, table is @2);
hi
this script using cross table
creates 3 new fields : service type values A,B,C
day type values: Weekend,Weekday
hours type values : Provided, refused
now you can build which ever chart you need
attach is an example
[_temp_fe65a0ad-622a-e723-a2f6-db1d9088]:
CROSSTABLE ([Sheet1.Attribute field],[Sheet1.Data field],3)
LOAD
[cation],
[Date],
[Customer ID],
[Service A Weekend Hours Provided],
[Service A Weekend Hours Refused],
[Service A Weekday Hours Provided],
[Service A Weekday Hours Refused]
FROM [lib://AttachedFiles/community.xlsx]
(ooxml, embedded labels, table is Sheet1);
[Sheet1]:
NOCONCATENATE LOAD
[cation],
[Date],
[Customer ID],
[Sheet1.Attribute field],
[Sheet1.Data field],
if(index([Sheet1.Attribute field],'Weekend')>0,'Weekend','Weekday') AS [day type],
if(index([Sheet1.Attribute field],'Provided')>0,'Provided',
'Refused') AS [hours type],
mid([Sheet1.Attribute field],9,1) AS [service type]
RESIDENT [_temp_fe65a0ad-622a-e723-a2f6-db1d9088];
DROP TABLE [_temp_fe65a0ad-622a-e723-a2f6-db1d9088];
I managed to get the load done as intended. One note is that I had to create the load in two passes. One for the cross-table measures, and one for some additional row specific measures. Qlik created a synthetic key that joined my cross table with the row-specific measures. (location&date&ID) as key.
Thanks for your help.