topic Re: Star Schema Key Definition in QlikView

Star Schema Key Definition

marco_puccetti — Wed, 29 Jul 2015 10:09:18 GMT

Hello, i need to know how to the following thing:

Table A

Key A

Field A

Field B

Table B

Key B

Field C

Field D

I need to have a Table C Having the Key of both A and B Tables! Is there any simply method to do this?

Table C

Key A

Key B

Field E

Field F

Thanks

Marco

Re: Star Schema Key Definition

avinashelite — Wed, 29 Jul 2015 10:12:08 GMT

we have many ways to do this:

*concatenation

*join

*applymap

etc

based on the data we can decided

Re: Star Schema Key Definition

avinashelite — Wed, 29 Jul 2015 10:15:43 GMT

simple way would be

Table A

Key A

Field A

Field B

Table B

Key B

Field C

Field D

Table C

LOAD

key C as Key

from

tabel C

Load

Key A as Key

resident

Table A

Load

Key B as Key

resident

Table B

Re: Star Schema Key Definition

tresB — Wed, 29 Jul 2015 10:16:37 GMT

May be linked table is what you need. Check this blog post: Concatenate vs Link Table

Re: Star Schema Key Definition

marco_puccetti — Wed, 29 Jul 2015 10:43:54 GMT

So if Table C must be composed by all its fields plus all the keys needed how can i do by code?

Table C

....

Key A as Key, (from table A)

Key B as Key (from table B)

Thanks

Marco

Re: Star Schema Key Definition

Anonymous — Wed, 29 Jul 2015 10:58:01 GMT

You may use a linked table as Tresesco mentioned. Just make sure that you create a key on the dimensions itself and then join it to the Linked table you have created

Re: Star Schema Key Definition

marco_puccetti — Wed, 29 Jul 2015 11:01:12 GMT

I have tried also with this statement in order to create a linked table but it is not properly working.

LINK_TABLE:

LOAD NURELAZ AS "NURELAZ" FROM $(QVD_PATH)\SVGES108.QVD (qvd);

LOAD NURELAZ &'#'& PRGMOV AS "KEY" FROM $(QVD_PATH)\SVGES116.QVD (qvd);

Thanks

Re: Star Schema Key Definition

puttemans — Wed, 29 Jul 2015 11:09:06 GMT

Hi Marco,

Importing the keys will not help, since they need a link with the underlying data in table C. Can you reconstruct the 2 keys with the data you'll load in C? This would be the simplest.

Else you need to identify a common field between table A/B and table C, and work with a mapping solution.

Regards,

Johan

Re: Star Schema Key Definition

marco_puccetti — Wed, 29 Jul 2015 12:12:44 GMT

Yes but i need the table A and Table B.

Thanks

Marco

Re: Star Schema Key Definition

marco_puccetti — Wed, 29 Jul 2015 13:47:02 GMT

LOAD

FROM

xx.QVD (qvd);

LEFT JOIN

LOAD

NURELAZ &'#'& PRGMOV AS "KEY"

FROM

yy.QVD (qvd);

It would create a table A with all of its field with the KEY element isn't it?

When i run it it works but the application pause always, can you help me?

Thanks

Marco

Re: Star Schema Key Definition

marco_puccetti — Wed, 29 Jul 2015 14:54:26 GMT

Is it correct?

Thanks

Marco

Re: Star Schema Key Definition

puttemans — Thu, 30 Jul 2015 11:23:20 GMT

Hi Marco,

You need to have a link with the key in order for the associative model to work. It would thus rather look like this :

LOAD

X as Key,

FROM

xx.QVD (qvd);

LOAD

A as Key, or B-C as Key or B+D as Key

FROM

yy.QVD (qvd);

As long as the content of your variable Key in both tables is the same.