topic Re: Data Modeling Thoughts in QlikView

Data Modeling Thoughts

alec1982 — Mon, 28 Apr 2014 16:10:19 GMT

Hi guys,

I have two tables and simply I want to join them using couple of columns. data within the tables can be joined based if one of the two columns match or both.. Any idea on how to do that?

Table1:

SO RepName Amount

1 John 250

2 Jared 300

3 Mike 150

Table2:

SO RepName AR

1 John ABC

1 Alicia KLM

2 Jared yui

4 Mike qwe

Thxs,

Alec

Re: Data Modeling Thoughts

MK_QSL — Mon, 28 Apr 2014 16:12:16 GMT

What is SO here?

Re: Data Modeling Thoughts

its_anandrjs — Mon, 28 Apr 2014 16:13:44 GMT

You can join with common keys and check would be unique.

Re: Data Modeling Thoughts

its_anandrjs — Mon, 28 Apr 2014 16:18:55 GMT

In this two tables i believe one is master and second one is transaction table please clear why SO has different behavior and values.

Re: Data Modeling Thoughts

its_anandrjs — Mon, 28 Apr 2014 16:23:41 GMT

You can load your table by joining them but data in table is not in proper check and confirm please

LOAD * Inline

[

SO, RepName, Amount

1, John, 250

2, Jared, 300

3, Mike, 150

];

Join

Table2:

LOAD * Inline

[

SO, RepName, AR

1 , John, ABC

1, Alicia, KLM

2, Jared, yui

4, Mike, qwe

];

Re: Data Modeling Thoughts

alec1982 — Mon, 28 Apr 2014 17:11:46 GMT

Hi guys,

Thank you for your help but i should've been more clear on what i need. I need to keep both tables within the QV and not join them into one table.. SO is Sales Order nr and it is unique on the first table only.

Thxs

Re: Data Modeling Thoughts

its_anandrjs — Mon, 28 Apr 2014 17:32:38 GMT

Then leave the tables in the link condition with any same key like SO.

Re: Data Modeling Thoughts

alec1982 — Mon, 28 Apr 2014 17:34:59 GMT

That will create Synthetic keys which is wrong..

Re: Data Modeling Thoughts

MK_QSL — Mon, 28 Apr 2014 18:17:41 GMT

OK... One question... Is SO unique to 1st Table?

Why I am asking, is SO 1 from Table 1 is Unique, so how come SO 1 in table 2 is having two different representative?

Re: Data Modeling Thoughts

Joseph_Musekura — Mon, 28 Apr 2014 18:44:04 GMT

Hi,

Please check examples given in this link " Understanding Join, Keep and Concatenate".

http://community.qlik.com/thread/39177

/joseph

Re: Data Modeling Thoughts

alec1982 — Mon, 28 Apr 2014 19:05:44 GMT

Hi guys,

thank you all for your help. I guess i found what I need by concatenate the two tables and filter based on the two columns listed above. This has been a great solution so far. Thxs for all your help!

Re: Data Modeling Thoughts

preminqlik — Mon, 28 Apr 2014 19:07:25 GMT

just concatenate it..

Re: Data Modeling Thoughts

its_anandrjs — Mon, 28 Apr 2014 19:13:29 GMT

Concatenate is good solution but i believe you have to make the link tables and then use the tables but depend on the table performance after join or concatenate how you use the tables.

And when concatenate make a flag field in both the tables like below

Table1:

LOAD *,'Tab1' as Flag;

LOAD * Inline[

SO, RepName, Amount

1, John, 250

2, Jared, 300

3, Mike, 150];

Concatenate

Table2:

LOAD *,'Tab2' as Flag;

LOAD * Inline[

SO, RepName, AR

1, John, ABC

1, Alicia, KLM

2, Jared, yui

4, Mike, qwe];

Regards

Re: Data Modeling Thoughts

Anonymous — Mon, 28 Apr 2014 19:17:27 GMT

Hi,

Table1:

SO RepName Amount

1 John 250

2 Jared 300

3 Mike 150

Table2:

SO RepName AR

1 John ABC

1 Alicia KLM

2 Jared yui

4 Mike qwe

you can face a problem do to concatenating the tables,you can't show the reports based on AR dimension,please check the requirement how you want to create .reports.after check the data model,

Thanks

SHAIK

Re: Data Modeling Thoughts

Joseph_Musekura — Tue, 29 Apr 2014 08:34:20 GMT

Hi,

Jsut a small notice.

once your are OK with an answer remember to mark the question as answered

Regards,

Re: Data Modeling Thoughts

alec1982 — Tue, 29 Apr 2014 13:00:15 GMT

Thank you for your reminder.. Even I am the one who created this discussion I am not able to close it with correct answer. the option doesn't show on my screen while i am logged in. Any idea?

Re: Data Modeling Thoughts

Joseph_Musekura — Tue, 29 Apr 2014 13:52:04 GMT

Hi,

I am not sure that I think you need to mark a correct answer before other steps