3 Replies Latest reply: Aug 24, 2018 3:56 PM by Todd Buss RSS

    Add intersecting dimensions during load

    Todd Buss

      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.

        • Re: Add intersecting dimensions during load
          Piet Hein van der 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);

          • Re: Add intersecting dimensions during load
            Liron Baram

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

              • Re: Add intersecting dimensions during load
                Todd Buss

                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.