Add intersecting dimensions during load

[toddbuss]

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.

[stigchel]

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);

[lironbaram · Accepted solution]

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];

[toddbuss]

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.