connecting a crosstable and a hierarchy?

[obmk]

Hi,

I have 2 tables. The first contains a kind of raw report where many items are documented. Each Item can have several values, i.e.(see first table in picture):

Caller 222 reports a "2" under Item1, caller 333 reports a "1".

The meaning (the semantic so to say) of the item content (I call them "values") is defined in another table (see the second).

What I've done so far:

a.) loading the first table as a crosstable:

crosstble (Items, ItemValue, 5)
load *

from foobar.xls
(...);

b) loading the table with all the definitions as a hierarchy:
hierarchy ( IdemID, Item_Value, Item_label, ...)

load

ItemID,
item_value,
item_label

from
whatever.xls
...
;

What I need: a good idea who to connect the itemvalues from the crosstable with item_value of the hierarchy so that I can get the item_labels

Thanks for any hint.

Best Regards,

snoman

[montubhardwaj]

Hi not very sure whether this will click or not.... but possibly what you can do is load the fields(Item1,Item2 ,Item3 etc.) from the first table. Then in the script Union all these fields so that you get a single field with all Items like 1,2,6,7,8,9 etc(assuming that item values is unique). I am calling this field as All_Item.

Then you can load the fields from 2nd table and can join to the first table on All_Item to the 2nd table on Item_value. In this way you will get item label for each of the item.

Cheers,

Sharma

[obmk]

Thanks for the quick answer. Really sounds like a way to get access to the labels. I will try that today but I must confess I'd prefer a solution within the crosstable/hierarchy construct or perhaps setting up an aditional table to connect the two tables by the temIDs... ore something like that. I already had a first look to the hierarchybelongsto command but if I got it right there is no way to set a field from another table as the parentID... 😕