Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

toddbuss
Contributor

Add intersecting dimensions during load

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:

WeekdayWeekend
Hours of Services Acceptedxx
Hours of Services Refusedxx

Each of the Excel tables flattens the matrix into distinct rows:

LocationDateCustomer IDService A Weekend Hours ProvidedService A Weekend Hours RefusedService A Weekday Hours ProvidedService A Weekday Hours Refused...
northjan001.511
westjan002.51
eastfeb003.531
westfeb0021
...

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.

1 Solution

Accepted Solutions
Partner
Partner

Re: Add intersecting dimensions during load

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

3 Replies
Partner
Partner

Re: Add intersecting dimensions during load

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

Partner
Partner

Re: Add intersecting dimensions during load

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
Contributor

Re: Add intersecting dimensions during load

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.