Forum Discussion
Using PATH(), How Do I Remove Blanks From My Hierarchy Between Levels?
Hi I_NeedMorePower,
Please provide some sample data to help clear your table structure.
Regards,
Xiaoxin Sheng
Here is an example:
RetailProductHierarchyCategories Table:
CategoryName ParentCategoryName
TopCategory NULL
Cakes TopCategory
Drinks TopCategory
Soft Drinks Drinks
Cola Soft Drinks
Hot Drinks Drinks
Tea Hot Drinks
-------------------------------------------
The previous table has 4 levels of categories: TopCategory > Drinks > Soft Drinks > Cola.
After applying the steps I descriped in the main post, making a PATH column and creating 4 columns for each level and applying the PATHITEM() function for each column, the previous table with resulted collumns will look like this:
CategoryName ParentCategoryName CategoryL1 CategoryL2 CategoryL3 CategoryL4
TopCategory NULL TopCategory Blank Blank Blank
Cakes TopCategory TopCategory Cakes Blank Blank
Drinks TopCategory TopCategory Drinks Blank Blank
SoftDrinks Drinks TopCategory Drinks SoftDrinks Blank
Cola SoftDrinks TopCategory Drinks SoftDrinks Cola
HotDrinks Drinks TopCategory Drinks HotDrinks Blank
Tea HotDrinks TopCategory Drinks HotDrinks Tea
-------------------------------------------------------------------------
The result hierarchy will look like this:
>TopCategory
>Blank
>Blank
>Blank
>Cakes
>Blank
>Blank
>Drinks
>Blank
>Blank
>Soft Drinks
> Blank
> Cola
>Hot Drinks
>Blank
> Tea
...............................................................................................
As you can see there are blanks between each level till the 4th level. I believe it's because it reads the blanks in the table.
I hope I calrified the things with this humble sketch haha.