Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Loading hierarchy excel file

Hi All,

I have here an example of table I need to load in my application. Please see attached file.

Any advice you can give? Thank you.

Note: This excel file is loaded monthly and changes value for IDs assigned per level.

3 Replies
Employee
Employee

Re: Loading hierarchy excel file

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.

HIC

Not applicable

Re: Loading hierarchy excel file

^bump! need help on how to format it that way... without doing it manually...either by macro or scripting..

Employee
Employee

Re: Loading hierarchy excel file

OK. Try this with your data:

ExpandedNodes:

LOAD ID, LVL1, LVL2,

  If(Len(Trim(LVL3))>0, LVL3, If(LVL2=Peek(LVL2), Peek(LVL3))) as LVL3,

  LVL4;

LOAD ID, LVL1,

  If(Len(Trim(LVL2))>0, LVL2, If(LVL1=Peek(LVL1), Peek(LVL2))) as LVL2,

  LVL3, LVL4;

LOAD

  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);

HIC

Community Browser