3 Replies Latest reply: Apr 3, 2015 5:27 AM by Gysbert Wassenaar RSS

    Processing field arrays/multiple columns of same data elements

    Kirby Davis

      I'm having a data modeling/loading issue that I need some assistance with.

       

      My data, which is coming from a relational database, contains records that have multiple fields for the same information. For example, a customer record may have from zero (0) to five (5) licenses associated with it, defined as:

        ...

        LIC_COUNT,

        LIC_NUMBER_1,

        LIC_TYPE_1,

        LIC_STATUS_CODE_1,

        LIC_NAME_1,

        LIC_EFF_DATE_1,

        LIC_END_DATE_1,

        LIC_NUMBER_2,

        LIC_TYPE_2,

        LIC_STATUS_CODE_2,

        LIC_NAME_2,

        LIC_EFF_DATE_2,

        LIC_END_DATE_2,

        LIC_NUMBER_3,

        LIC_TYPE_3,

        LIC_STATUS_CODE_3,

        LIC_NAME_3,

        LIC_EFF_DATE_3,

        LIC_END_DATE_3,

        LIC_NUMBER_4,

        LIC_TYPE_4,

        LIC_STATUS_CODE_4,

        LIC_NAME_4,

        LIC_EFF_DATE_4,

        LIC_END_DATE_4,

        LIC_NUMBER_5,

        LIC_TYPE_5,

        LIC_STATUS_CODE_5,

        LIC_NAME_5,

        LIC_EFF_DATE_5,

        LIC_END_DATE_5,

               ...

       

      What is the best way to load this data into Qlik Sense such that only one of each field (e.g., LIC_NUMBER, LIC_TYPE, LIC_STATUS_CODE, LIC_NAME, LIC_EFF_DATE,LIC_END_DATE) and containing from zero to five data values is associated with each customer in Qlik Sense?

       

      Any assistance would be greatly appreciated!

        • Re: Processing field arrays/multiple columns of same data elements
          Gysbert Wassenaar

          Since you have a fixed maximum of five licenses per customer it's probably easiest to use five load statements:

           

          Data:

          LOAD

               CustomerID,

               ...other fields....,

               LIC_NUMBER_1 as LIC_NUMBER,

               LIC_TYPE_1 as LIC_TYPE,

               LIC_STATUS_CODE_1 as LIC_STATUS_CODE,

               LIC_NAME_1 as LIC_NAME,

               LIC_EFF_DATE_1 as LIC_EFF_DATE,

               LIC_END_DATE_1 as LIC_END_DATE

          FROM ...

          WHERE Len(Trim(LIC_NUMBER_1)) >0

          ;

           

          CONCATENATE (Data)

          LOAD

               CustomerID,

               ...other fields....,

               LIC_NUMBER_2 as LIC_NUMBER,

               LIC_TYPE_2 as LIC_TYPE,

               LIC_STATUS_CODE_2 as LIC_STATUS_CODE,

               LIC_NAME_2 as LIC_NAME,

               LIC_EFF_DATE_2 as LIC_EFF_DATE,

               LIC_END_DATE_2 as LIC_END_DATE

          FROM ...

          WHERE Len(Trim(LIC_NUMBER_2)) >0

          ;

           

          CONCATENATE (Data)

          LOAD

               CustomerID,

               ...other fields....,

               LIC_NUMBER_3 as LIC_NUMBER,

          ...etc