Hierarchy With Node Empty

[adamgaox]

Hi All,

I've come across a problem during creating Hierarchy Tree with a table like below

LEVEL 1	LEVEL 2	LEVEL 3	LEVEL 4	LEVEL 5
A	A01	A01-X1	A01-X1-Y1	A1234
A	A02	A02-X1	A02-X1-Y1	A1235
A		A02-X2	A02-X2-Y1	A1236
B	B01	B01-X1	B01-X1-Y1	B1234
B	B02	B02-X2	B02-X2-Y1	B1235
B		B02-X3	B02-X3-Y1	B1236
C	C01	C01-X1	C01-X1-Y1	C1234
C		C01-X2	C01-X2-Y1	C1235

as we can see A1236 belongs to A,B1236 belongs to B,C1235 belongs to C

after hierarchy we'll get A1236,B1236,C1235 belongs to A,B,C

for futher if every mid level (Level2,Level3,Level4) with value empty,much more Level5 Code will point to the Level1 does not match the table.

Is there a way to solve this problem?Thanks in advance.

[sujeetsingh]

Need to handle the null()s

[adamgaox]

Hi SU,

I'm puzzled.I think it's not a specific situation.When developing QVWs for clients,their data won't be perfect all the time.Once we facing it how to overcome it?

[sujeetsingh]

Why do not you use exist function to either remove them or to represent them as "Unknown"

[martinpohl]

expand your script by the replace-command.

This will replace empty hierarchy levels with the level above

example:

FROM

your data.xlsx

(ooxml, embedded labels, table is Tabelle1, filters(Replace(2, left, StrCnd(null)),Replace(3, left, StrCnd(null)),Replace(4, left, StrCnd(null))

));

Regards

[adamgaox]

Use Exists will reduce data collection,I think it's not the prefect method,

Use Unknown we'll still see unknown belongs to different Level1 Level2 and etc.