topic Re: Creating Key column in DIM and Loading Fact? in QlikView

Creating Key column in DIM and Loading Fact?

shyamcharan — Tue, 09 Feb 2016 04:08:31 GMT

Hi Experts,

I have a few dimension tables which joins to the Fact table on a key column. However, the key column has string values with a length of 38 characters.

To avoid joining the tables on String based key columns, I am trying to create Auto number column in the dimension tables.

However, loading the fact tables is challenging for me.

Do i use ApplyMap() function in fact table to call in the newly created auto number column values from the dimensions?

Any help here is highly appreciated.

Thanks in advance.

--Shyam

Re: Creating Key column in DIM and Loading Fact?

senpradip007 — Tue, 09 Feb 2016 04:40:16 GMT

Can you upload some sample data?

Re: Creating Key column in DIM and Loading Fact?

ziadm — Tue, 09 Feb 2016 04:50:18 GMT

Have you tried using AutoNumberHash128 creating a key from multiple fields

Re: Creating Key column in DIM and Loading Fact?

harsh44_bhatia — Tue, 09 Feb 2016 04:53:44 GMT

hello,

Could you elaborate what difficulties you are facing while loading the fact tables.

Just to help you out.

when you try to use multiple autonumber() generated keys, give index as well.

for eg:

Employeedim:

load

autonumber(employeeID,'employeeIndex') as employeeKey,

from employee.qvd;

Customerdim:

load

autonumber(CustomerID,'customerIndex') as customerKey,

from customer.qvd;

fact:

load

autonumber(employeeID,'employeeIndex') as employeeKey,

autonumber(CustomerID,'customerIndex') as customerKey,

from fact.qvd;

Re: Creating Key column in DIM and Loading Fact?

jonathandienst — Tue, 09 Feb 2016 05:03:49 GMT

I suggest that you upload a sample qvw or at least your script. I would not use ApplyMap(), you can just use AutoNumber on the key fields for both the dimension and the fact tables. Note that Autonumber() resets for each reload, so the autonumber for the fact and dimension must be generated in the same reload.

Re: Creating Key column in DIM and Loading Fact?

avinashelite — Tue, 09 Feb 2016 05:04:54 GMT

Try to use the Hashkey or Auto haskey....that should be better ...

Re: Creating Key column in DIM and Loading Fact?

shyamcharan — Tue, 09 Feb 2016 05:40:17 GMT

Thank you all.

Please find the attached qvw sample document with 10 records in it.

I have used one DIM_TABLE and FACT_TABLE in the document.

Each of these are joined on RVA_PK column.

However, they are string column. I am trying to join them using a Numeric key column rather the string to improve the performance.

I am trying to create a numeric column in dimension table. Once this is done I would like to load the fact table and join them on new key column.

Any further help on this is highly appreciated.

Thanks in advance.

Shyam.

Re: Creating Key column in DIM and Loading Fact?

shyamcharan — Tue, 09 Feb 2016 23:08:55 GMT

Hi All,

I have used the Autonumber(Field) script function in Dimension and in Fact Table and used these new Autonumber(Field) as Key to join both these tables.

Note:Please check the attached sample QVW doc I have uploaded

I understood if both the tables are in the same load then unique values are generated in these both tables. Moreover, the generated values will be same in both the tables if the Field used to generate Autonumber has same values.

For example:

Table1

Col1 Col2 Autonumber(Col1)

abc 1234 1

XYZ 976 2

lkj 897 3

Table2

ColX ColY Autonumber(Col1)

abc 1234 1

XYZ 976 2

poi 234 4

Now I can use the new Autonumber(Col) to join both these tables.

Please correct me if I am wrong.

Please check the attached sample QVW doc I have uploaded

Re: Creating Key column in DIM and Loading Fact?

shyamcharan — Fri, 12 Feb 2016 01:20:41 GMT

Hi All,

I have created a new document as an example. Here I have used three tables and used Autonumber() function on all of them.

However, I am not getting the wrong results.

As shown in the attached example, why I am not able to see values under C1 or C2 when I select C5=Northern Place?

Please view attached example.

I appreciate any help on this. Thanks in advance.

Regards,

Shyam.

Re: Creating Key column in DIM and Loading Fact?

rwunderlich — Fri, 12 Feb 2016 04:32:35 GMT

You have autonumbered O4 in T3. You must also autonumber O4 in T2 to create matching values

-Rob