topic Re: Data modelling in QlikView

Data modelling

yogitamehta10 — Tue, 18 Jul 2017 15:10:10 GMT

HI All,

I just need some clarification.

I had two tables in which most of the fields were same but some were different mainly all the dimensions fields were same .

I had to merge in to in a single table where for each dimension like brand if we have value in both the table then it should come otherwise blank.

I tried to achieve it by concatenation but some of the reason it was giving duplicate records then I tried full join but then the issue I was getting is if suppose I select brand from table 1 then it will not show the the data of brand which are in table 2

for this reason I used link table.

now I have

table 1

dim1

dim2

dim3

dim1&’’&dim2&’’& dim3

datafield1

datafield2

from abc;

outerjoin(table1)

table2

dim1

dim2

dim1&’’&dim2&’’& dim3

datafield3

datafield4

from xyz

now what I’m doing

linktable:

dim1

dim2

dim1&’’&dim2&’’& dim3

from abc

concatenate (linktable)

dim1

dim2

dim1&’’&dim2&’’& dim3

from Xyz

is this right approach

Re: Data modelling

yogitamehta10 — Wed, 19 Jul 2017 13:05:02 GMT

any response??

Re: Data modelling

Anil_Babu_Samineni — Wed, 19 Jul 2017 13:10:00 GMT

Looks good.. Do you feel any bad or simple asking? Create one key and do concatenate and then Link table, May be?

Re: Data modelling

yogitamehta10 — Wed, 19 Jul 2017 13:14:45 GMT

Do I need to join table 1 and table 2 or I don't need to

Re: Data modelling

suryaa30 — Wed, 19 Jul 2017 13:58:46 GMT

Simple concatenate would do. I

If you you say that you are getting duplicates what exactly do you mean? are you not able to identify the records from which table it has come? because duplicates arise only when you have 2 records with all fields matching after concatenation but they are from different table originally.

In that case add a dummy field indicating the origin table before concatenate.

table 1:

dim1

dim2

dim3

dim1&’’&dim2&’’& dim3

datafield1

datafield2

'table1' as tableIdentifier

from abc;

Concatenate(table1)

table2:

dim1

dim2

dim1&’’&dim2&’’& dim3

datafield3

datafield4

'table2' as tableIdentifier

from xyz

Re: Data modelling

yogitamehta10 — Wed, 19 Jul 2017 14:26:30 GMT

no concatenation will not work for me as I have id field in both the table

table 1

table 2

id 1,2,3,4,

so if I have concatenate it it will generate overall 7 rows instated it should be only 4 rows.

Re: Data modelling

Anil_Babu_Samineni — Thu, 20 Jul 2017 04:06:36 GMT

If you get any Synthetic keys, May be required to use JOIN or i don't think so ?