Separated data and dictionary

[arsenal1983]

Hello,

In my fact table I have customers. Each customer can have one or more product. All the products the customer has are in one column: ID separated by mark "|".

CUST_ID	PRODUCTS
1	200885|200886
2	200887|200888
3	200889|200890
4	200891|200892|200894
5	200893|200894
6	214609|214609
7	214609|214609|214613
8	214609|214609|214613|214613
9	214609|214609|214614
10	214609|214609|228890
11	214609|214610|214611|214612|214613|214614|214615|214616
12	214609|214610|214611|214612|214613|214614|214615|214616|214630|214631
13	214609|214610|214613|214614
14	214609|214610|214614
15	214609|214611
16	214609|214611|214613
17	214609|214611|214613|214615
18	214609|214611|214615
19	214609|214613

I would like to join the fact table with the product dictionary (product ID as a key).

How can I do this?

[Brett_Bleess]

Best I can offer is to have a look around in the Design Blog area of Community, there are a lot of how-to posts there, but I am not exactly sure which one(s) may be helpful in your case, so I am going to let you dig around on your own to see if you can find what you need.

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

This sort of reminds me a bit of a Bill of Materials type use case, so maybe this post might help:

https://community.qlik.com/t5/Qlik-Design-Blog/Bill-of-Materials/ba-p/1462792

Sorry I am not of more help, but hopefully this may give you some further ideas on how to approach things.

Regards,
Brett

[Saravanan_Desingh · Accepted solution]

One way to create the Product Dictionary.

tab1:
LOAD CUST_ID, SubField(PRODUCTS,'|') As PRODUCT_ID
;
LOAD * INLINE [
    CUST_ID, PRODUCTS
    1, 200885|200886
    2, 200887|200888
    3, 200889|200890
    4, 200891|200892|200894
    5, 200893|200894
    6, 214609|214609
    7, 214609|214609|214613
    8, 214609|214609|214613|214613
    9, 214609|214609|214614
    10, 214609|214609|228890
    11, 214609|214610|214611|214612|214613|214614|214615|214616
    12, 214609|214610|214611|214612|214613|214614|214615|214616|214630|214631
    13, 214609|214610|214613|214614
    14, 214609|214610|214614
    15, 214609|214611
    16, 214609|214611|214613
    17, 214609|214611|214613|214615
    18, 214609|214611|214615
    19, 214609|214613
];