If you can create this file in a different way, it would be a lot easier. In the attachment, you can find two ways of defining hierarchies that you can use in QlikView.
The Expanded Nodes you can load as it is. The Adjacent Nodes you can load using the Hierarchy prefix.
Testhier.xlsx 9.4 K
OK. Try this with your data:
LOAD ID, LVL1, LVL2,
If(Len(Trim(LVL3))>0, LVL3, If(LVL2=Peek(LVL2), Peek(LVL3))) as LVL3,
LOAD ID, LVL1,
If(Len(Trim(LVL2))>0, LVL2, If(LVL1=Peek(LVL1), Peek(LVL2))) as LVL2,
RecNo() as ID,
If(Len(Trim(LVL1))=0, Peek(LVL1), LVL1) as LVL1,
LVL2, LVL3, LVL4
FROM [Testhier.xlsx] (ooxml, embedded labels, table is Sheet1);