Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
Hi Gysbert,
Thanks for the reply. A couple of things that I didn't mention previously are:
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
Maybe. Have a look at the CrossTable function: The Crosstable Load