Processing field arrays/multiple columns of same data elements

[#1]

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!

[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

[#3]

Hi Gysbert,

Thanks for the reply. A couple of things that I didn't mention previously are:

• There are about 500,000 records in the database table that I'm pulling from.
• For each of those 500,000 records, in additional to the other unique/distinct fields, there are nine separate field groupings per record, each containing from zero to five data values. The license information mentioned in my first post is one of the nine groupings associated with each record.

With this additional information would there be a more efficient way to load the information rather than repeatedly reading through the source data multiple times?

Thanks,

... Kirby

[Gysbert_Wassenaar]

Maybe. Have a look at the CrossTable function: The Crosstable Load