How to join 3 or more tables

[#1]

Hi guys,

I'm pretty new to the Qlik-Tools, but I've already find out how to join two tables.

Here is my code which works pretty well.

VBAK:

LOAD *, VBAKVBELN as key FROM
[\\VBAK.csv]
(txt, codepage is 1252, embedded labels, delimiter is ';', msq);

inner join (VBAK)

LOAD *, VBAPVBELN as key FROM
[\\VBAP.csv]
(txt, codepage is 1252, embedded labels, delimiter is ';', msq);

I would like to know, whether there is a kind of assistant which would help user to add and join another tables to this data model. Or at least how to do it manually.

My test scenario is to join several tables -> without using SAP connector. I have table VBAK and VBAP (as CSV), joined them on VBELN and need to join additional tables "CUOBJ-Table (with configuration)" on VBAP=CUOBJ. Is there someone who knows how to define another key in VBAP-CUOBJ and in inner join define the connection on custom table "CUOBJ-Table"?

I and another user will appreciate your help.

Thank you very much in advance for your help.

Regards

Stan

[gandalfgray]

Hi Stan

There is no assistant in QV for joins as far as I know.

Joining (and/or avoiding joins) are a  central part of Qlikview load scripting.

Read as much as possible about it in the manuals.

In QV tables are by default linked (or joined, if you specify a join load) on fields with the same name (NB! case sensitive)

So if the tables do not already have a common field with the exact same name you need to rename one (or both) using As just as you did.

To connect (link or join) a third table you may need to rename one of the fields in the third table as you already did.

Note that you do not define "keys" in qlikview,

all fields that have exactly the same name as another field by default works as "keys".

[#3]

Thank you very much, Mr. Gandalf Gray.

I didn't know that about key definition. Is there any way how to switch off the automated connection based on same names and set it on my own? As example field ERDAT which has nothing common in VBAP and VBAK would create an automated join, but I do not need it. (one position has been created later than the sales document itself).

And another, hopefully my last modelling question. Is it possible to eliminate repeated values from VBAK (head) after the table was connected on VBAP (position).

Head:

Order 10001, Sum 480 inkl. VAT.

Position:

Order 10001, Pos 1, Sum 100 (exkl VAT).

Order 10001, Pos 2, Sum 100 (exkl VAT).

Order 10001, Pos 3, Sum 200 (exkl VAT).

Result after Join.

Order 10001, Pos 1, HeadSum 480, Sum 100.

Order 10001, Pos 2, HeadSum 480, Sum 100.

Order 10001, Pos 3, HeadSum 480, Sum 200.

Is it possible to eliminate Sum(HeadSum) / (count lines VBAP) = 1440 / 3.  ?? Are there some standard tools for such a solutions?

Regards

Stan

[ToniKautto]

QlikView is associative and thereby handles fields with the same name as key fields, this is not a feature that you can turn off. The easiest way to break a field associastion is to simply rename either of the fields by using AS.

LOAD

     FieldName AS NewFieldName

FROM datafile.qvd;

QlikView also offers a feature to automatically makes fields unique, by renaming them as tablename.fieldname

By using QUALIFY ERDAT; all the ERDAT fields will be renamed by QlikView. To stop the qualification, you simply use the UNQUALIFY command like UNQUALIFY ERDAT;

To get a quick start to how QlikView works and how you can utilize the features it is recommended to go through the free online training offered at QlikView's website;

http://qlik.com/Training

For your final question I am not sure what you intend to do. You want to eliminate row with certain results or the entire column?

[gandalfgray]

Hi Stan

I'm not sure I understand you correctly, but if you don't want to duplicate the data from the "head" table, do not join the "rows" table with the "head" table, simply let them be connected by a common field:

VBAK:

LOAD *, VBAKVBELN as key FROM
[\\VBAK.csv]
(txt, codepage is 1252, embedded labels, delimiter is ';', msq);

VBAP:

LOAD *, VBAPVBELN as key FROM
[\\VBAP.csv]
(txt, codepage is 1252, embedded labels, delimiter is ';', msq);

[#6]

Well, Gandalf,

I do not want to duplicate rows from head. I want to eliminate those repeated values. Please see my example above.

If in head row is 480 then this value will be shown 3 times after the join with position. Is there any possibility to supress repeated values from head to get result only 480 and not 3x480 ?

Stan

[ToniKautto]

This does not make sense to me. If you join tables their content will be combined into a new table, so to eliminate the join you simply do not join in the first place. Maybe I am missing out on exactly what your are trying to accomplish.

Just as Gandalf said, if you do not want to have the value repeated in your data model then you shoudl leave them in separate tables. By having the Head table linked to the Position table through the Order Id field, you will still be able analyse data based on this relation.

If this is still an issue for you, please explain why it is a problem for you to have the value repeated in the joined table.

[#8]

Toni,

the goal is not to eliminate the join itself but only repeated values, numbers, key figures from previous table.

Have a look. I have following tables: Material Positions with amount and these positions are connected to configuration. Configuration table has for each position many rows.

Position 1, amount 15 -> configuration length = 100

Position 1, amount 15 -> configuration height = 5000

Position 1, amount 15 -> configuration colour = white

Position 2, amount 4 -> configuration length = 100

Position 2, amount 4 -> configuration height = 3000

Position 2, amount 4 -> configuration colour = blue

How many PCs were with length = 100 and height = 5000?

How to damn eliminate the tripple amount?

Stan

[ToniKautto]

I do not think that should be a practical problem, if your data and chart object have logical setup.

You can achive that by using Aggr() in your chart object, it is nothing that you need to handle in the script. So just load the table joined and then set up your chart object as you want to present the data, and if this still fails please attach a sample of your QVW to enable easier feedback on your solution.