Join two tables and then build a hierarchy

[#1]

Hello!

I'm new to qlikview and got a problem within the loadscript.

What i want to do, is to join parts of two different tables (which are both *.xlsx files during testing, but will be parts of a huge View in the future) and then build a hierarchy from this joined table.

Both steps are no problem on their own, but the combined

process does not work for me.

The first table is a whole department

view with several layers but it has no names but rather IDs. The second table is an "ID to text" table that consists of the TextIDs of all names in the future View and the according texts in german and english.

I think its obvious that i need the hierarchy to show the names of the departments - not the IDs. The only option i know to handle this is to join the second table into the first (INNER JOIN LOAD), with the argument, to only use the german (or english ) texts - i used "WHERE LANGID = 1".

But if i do so, the hierarchy argument returns an error upon execution of the script, saying that the string CTEXT" does not exist. Which is actualy right, because it does not exist in the first table but only in the second, that comes with the JOIN argument. I think that the loadscript does not understand, that the hierarchy argument should use the whole new table and not only the first.

I tried everything i could imagine and searched the Qlikcommunity, but i found nothing that would help me solve my problem.

Thanks for your help in advance!

Julian Birngruber

Here is the part of my loadscript that all is about:

HIERARCHYBELONGSTO (NKNOTENID, NTECHFATHER, TECHNAME, preID, preNAME)

LOAD NKNOTENID,

     NTEXTID,

     NTECHFATHER,

     .

     .

     .

FROM

[...]

(ooxml, embedded labels, table is ...);

INNER JOIN LOAD CTEXT AS TECHNAME,

                NTEXTID

FROM

[...]

(ooxml, embedded labels, table is ...)

WHERE NLANGID = 1;

[#2]

Hi Julian,

can you provide these two excel documents with sample data?

regards

[#3]

Of course!

Here are the two excel documents, limited to the columns that are essential in this case and filled with sample data.

TIA

[#4 · Accepted solution]

Hi Julian,

try with this script:

TECHVIEW:

LOAD NKNOTENID,

     NTEXTID,

     NTECHFATHER

FROM

TECHVIEW.xlsx

(ooxml, embedded labels, table is QQKOPERCO_T_TECHNOLOGIESICHT);

Inner join

TLANG:

LOAD NTEXTID,

     NSPRACHID,

     CTEXT

FROM

TLANG.xlsx

(ooxml, embedded labels, table is QQKOPERCO_T_TEXTSPRACHE);

TEMP_DE:

Hierarchy(NKNOTENID_TMP, NTECHFATHER_TMP, ID, PATH1, CTEXT_TMP, TREE_DE_TMP)

LOAD NKNOTENID,

     NKNOTENID as NKNOTENID_TMP,

     NKNOTENID as ID,

     NTECHFATHER as NTECHFATHER_TMP,

     CTEXT as CTEXT_TMP

Resident TECHVIEW where NSPRACHID=1;

TEMP_EN:

Hierarchy(NKNOTENID_TMP, NTECHFATHER_TMP, ID, PATH1, CTEXT_TMP, TREE_EN_TMP)

LOAD NKNOTENID,

     NKNOTENID as NKNOTENID_TMP,

     NKNOTENID as ID,

     NTECHFATHER as NTECHFATHER_TMP,

     CTEXT as CTEXT_TMP

Resident TECHVIEW where NSPRACHID=2;

FINAL_DE:

LOAD TREE_DE_TMP as TREE_DE, NKNOTENID Resident TEMP_DE;

FINAL_EN:

LOAD TREE_EN_TMP as TREE_EN, NKNOTENID Resident TEMP_EN;

drop Table TEMP_DE, TEMP_EN;

//------ End of script

In dashboard create 3 listboxes:

     - one with NSPRACHID field

     - one with TREE_DE field (In general tab check 'Show as treeview', with separator '/', in Layout tab set conditional with NSPRACHID=1)

     - one with TREE_EN field('Show as treeview', separator '/', in Layout tab set conditional with NSPRACHID=2)

regards

[#5]

Thank you very much!

Your answer was totaly solving my problem!