topic Re: Separated data and dictionary in QlikView

Separated data and dictionary

arsenal1983 — Fri, 21 Feb 2020 12:48:43 GMT

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?

Re: Separated data and dictionary

Brett_Bleess — Wed, 25 Mar 2020 20:32:49 GMT

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

Re: Separated data and dictionary

Saravanan_Desingh — Mon, 27 Apr 2020 01:53:03 GMT

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