Hierarchy problem

[kaldubai]

i have the following problem

i have this two columns in table

1-Recie:code for recipe such as CB_211

2-Item Code:

this field can be many things like packaging,raw material and Semifinished Good

i want to create a hierarchy where the sequence like Recipe >>Semifinished Good>>whatevery material 

im trying to create this hierarchy in order to be able to show it in a pivot table

any idea how i would do that

[dsharmaqv]

post the sample data and desired output pls

[kaldubai]

Thanks @Deepak Sharm for you replay

load * inline [

batch_no, Recipe, Item_code

170001,211_cb,SFTOF0002

170001,211_cb,RMMPC0005

170001,211_cb,RMSET0003

170001,211_cb,RMSET0004

170001,211_cb,RMOLT0001

170001,211_cb,RMOLT0002

]

so all of the codes that start With R Will come together in order to form a SemiFinished Good which in this case SFTOF002

the hierarchy i want should be in the follwing order

Recipe >>SF Product>>Raw Material

as far as i know the heirarchy function works with adjacent values not sure if would help me here

[dsharmaqv]

try this

Test:

load * inline [

batch_no, Recipe, Item_code

170001,211_cb,SFTOF0002

170001,211_cb,RMMPC0005

170001,211_cb,RMSET0003

170001,211_cb,RMSET0004

170001,211_cb,RMOLT0001

170001,211_cb,RMOLT0002

];

Temp:
Load



batch_no,
Recipe,
Left(Item_code,1) as [SF Product]

Resident Test;

[kaldubai]

     then i will have a temp table with all recipes and first of all item code

am i missing somthing here

[dsharmaqv]

Not necessary

If you are loading from some file or database then you just have to popolate one new column

Left(Item_code,1) as [SF Product]

so your code will be like

Temp:

Load



batch_no,
Recipe,

Item_code,

Left(Item_code,1) as [SF Product]

From

[File];

thats it

In case of inline load use temp table