topic Re: Creating Hierarchy with 2 Fields in QlikView

Creating Hierarchy with 2 Fields

Anonymous — Thu, 03 Mar 2016 17:41:26 GMT

I almost see the all Hierarchy examples with 3 fields I mean with 3 levels , Can someone please post an example of it with just 2 fields ,

I have 2 fields - Group and Function , Group is ParentId and Function is Child/Nodeid , I need to create a list box with Tree view structure .. which is used as a filter.

Thanks

Re: Creating Hierarchy with 2 Fields

avinashelite — Fri, 04 Mar 2016 06:19:25 GMT

In Hierarchy , every example has 3 field because 2 will be ID field and 1 will the name associated with that ID which will help you to easily identify the ID's with the name ..in your case try like this

Hierarchy (Child/Nodeid, Group , Child/Nodeid)

Re: Creating Hierarchy with 2 Fields

Fri, 04 Mar 2016 06:32:03 GMT

Hi Ramya ,

Have a nice day.

folloe the link

Hierarchy in QlikView

Re: Creating Hierarchy with 2 Fields

Anonymous — Fri, 04 Mar 2016 08:06:31 GMT

Here is how I have written the script , can someone help me.. where I am going wrong , I don't get any value in Path field.. I have tried with & and / .. I just get blank path field

Table1:
load
"Date",
"Group",
if(Isnull("FC"),'0',"Count")as "Count1",
if(Isnull("FC"),'0',"FC")AS "FC1",
"Count",
"FC",
"Service API",
"FCI",
"RYG_FCI",
"RYG_TP99",
"SLA_FCI",
"SLA_TP99",
"TP50",
"TP90",
"TP99",
"TP999"
;

SQL SELECT *
FROM "Operational Xcellence: Services Time Series bjznuur2b";

ServiceTimeSeries:
Hierarchy(NodeID,ParentID,NodeName,Path,'&',Depth)
Load
"Group" as ParentID,
"Function" as NodeID,
"Function" as NodeName,
if(WildMatch("Group",'*Payments*'),'Payments',
if(WildMatch("Group",'*Payroll*'),'Payroll',"Group")) as FCIGroup,
'' as Status,
*;

LOAD
"Date" as "ServiceTimeSeries.Date",
"Group",
"Service API" as "Function",
("Count1") as "RequestCount",
("FC1") AS "FCCount",
("Count") as "RequestCount_All",
("FC") AS "FCCount_All",
"FCI",
"RYG_FCI",
"RYG_TP99",
"SLA_FCI",
"SLA_TP99",
"TP50",
"TP90",
"TP99",
"TP999"
Resident Table1 where "Date" >= '9/1/2015';
Drop Table Table1;

Re: Creating Hierarchy with 2 Fields

kaushiknsolanki — Fri, 04 Mar 2016 08:10:54 GMT

Hi You can below script.

Hierarchy(NodeID,ParentID,NodeName,ParentName,NodeName,Path,'&',Depth)
Load
"Group" as ParentID,
"Function" as NodeID,
"Function" as NodeName,
if(WildMatch("Group",'*Payments*'),'Payments',
if(WildMatch("Group",'*Payroll*'),'Payroll',"Group")) as FCIGroup,
'' as Status,
*;

Regards,

Kaushik Solanki

Re: Creating Hierarchy with 2 Fields

avinashelite — Fri, 04 Mar 2016 08:12:31 GMT

try like this

HIERARCHY(GEOG_KEY, PARENT_KEY, GEOG_DESC, [GEOG PARENT NAME], GEOG_DESC, [HIERARCHY

GEOG], '|', 'HIERARCHY DEPTH')

ServiceTimeSeries:
Hierarchy(NodeID,ParentID,NodeName,,,Path,'&',Depth)
Load
"Group" as ParentID,
"Function" as NodeID,
"Function" as NodeName,
if(WildMatch("Group",'*Payments*'),'Payments',
if(WildMatch("Group",'*Payroll*'),'Payroll',"Group")) as FCIGroup,
'' as Status,

Re: Creating Hierarchy with 2 Fields

Anonymous — Fri, 04 Mar 2016 08:35:17 GMT

By running the script Avinash gave I still get blank Path ,

By running Kaushik's script I get some values but not as a Tree view and they don't seem to be right , I have selected & in the list box presentation

Attached my App .. the Data in the Function is Bit tricky it has lots of / that's the reason I chose & to split the data

Re: Creating Hierarchy with 2 Fields

Fri, 04 Mar 2016 08:37:39 GMT

Hi Ramya,

GEOGH:

HIERARCHY(ParentId, NodeId, NodeName, [GEOG PARENT NAME], NodeName, [HIERARCHY

GEOG], '|', 'HIERARCHY DEPTH')

LOAD

Group as ParentId,

Functionas as NodeId,

Functionas as NodeName

if(WildMatch("Group",'*Payments*'),'Payments',
if(WildMatch("Group",'*Payroll*'),'Payroll',"Group")) as FCIGroup,
'' as Status,

From

[..\..\Downloads\DataHierarchy.xlsx]

(ooxml, embedded labels, table is HIERARCHY);

Re: Creating Hierarchy with 2 Fields

Anonymous — Fri, 04 Mar 2016 09:49:17 GMT

I get a syntax error with the above script , can you please take a look at the attached app script? IF any one trying to check my app , it has an alternate state for the Tab just for info as adding new objects to the sheet might not show data unless its in default state.

Re: Creating Hierarchy with 2 Fields

Fri, 04 Mar 2016 10:01:57 GMT

Sorry ramya i am using personal edition.

Wait for more answers

Re: Creating Hierarchy with 2 Fields

avinashelite — Fri, 04 Mar 2016 10:09:57 GMT

did you tried this

Hierarchy(NodeID,ParentID,NodeName, , , ,Path,'&',Depth) 3 blank fields ??

Currently I don't have the QV licence ...please share the table snapshot s from the model (Hierarchy table )

Re: Creating Hierarchy with 2 Fields

Anonymous — Fri, 04 Mar 2016 17:56:18 GMT

ServiceTimeSeries:

Hierarchy(NodeID,ParentID,NodeName,,,Path,'&',Depth)
Load
FCIGroup as ParentID,
Function as NodeID,
Function as NodeName,
'' as Status,
*;

LOAD
"Date" as "ServiceTimeSeries.Date",
if(WildMatch("Group",'*Payments*'),'Payments',
if(WildMatch("Group",'*Payroll*'),'Payroll',"Group")) as FCIGroup,
"Service API" as "Function",
("Count1") as "RequestCount",
("FC1") AS "FCCount",
("Count") as "RequestCount_All",
("FC") AS "FCCount_All",
"FCI",
"RYG_FCI",
"RYG_TP99",
"SLA_FCI",
"SLA_TP99",
"TP50",
"TP90",
"TP99",
"TP999"
Resident Table1 where "Date" >= '9/1/2015';
Drop Table Table1;

Exit script;

and here are the screen shots of the data , actually I don't find the Path field created in the script,

Re: Creating Hierarchy with 2 Fields

Anonymous — Sun, 06 Mar 2016 21:22:33 GMT

Any help on this?

Re: Creating Hierarchy with 2 Fields

MarcoWedel — Sun, 06 Mar 2016 23:14:19 GMT

Hi,

try using

Hierarchy(NodeID,ParentID,NodeName,,NodeName,Path,'&',Depth)

and correct your data/logic as your Group and Function fields do not have too many common values, which they should to define a hierarchy. (In fact I didn't find a single common value).

hope this helps

regards

Marco

Re: Creating Hierarchy with 2 Fields

Anonymous — Sun, 06 Mar 2016 23:55:51 GMT

What do you mean by common values it is supposed to be grouping by related values in database?

here I selected Billing , and its related values are shows in the data , I was expecting that there is going to be a main group Billing and its related values will appear in subgroup ..

Re: Creating Hierarchy with 2 Fields

MarcoWedel — Mon, 07 Mar 2016 00:48:11 GMT

I guess a hierarchy load is not what you are looking for.

Try

LOAD Group&'&'&Function as GroupFunction

...

instead. And use GroupFunction in your tree view list box using '&' as separator.

hope this helps

regards

Marco

Re: Creating Hierarchy with 2 Fields

swuehl — Mon, 07 Mar 2016 00:48:57 GMT

If I understood correctly, you don't need a Hierarchy Load then, just create a new concatenated field to build the tree:

TREE:

LOAD Group & '/' & Function as Tree, Group, Function

INLINE [

Group, Function

Billing, renew

Billing, Subscribe

];

Concatenate (TREE)

LOAD Group as Tree, Group , Function

RESIDENT TREE;

Now use Tree field in the list box and enable tree view option.

Re: Creating Hierarchy with 2 Fields

Anonymous — Mon, 07 Mar 2016 01:39:16 GMT

Actually this was the first option I tried , but with this I am not able to select a Group value , please find the attached.Nothing happens when I select Group value

Re: Creating Hierarchy with 2 Fields

Anonymous — Mon, 07 Mar 2016 03:20:09 GMT

Got it .. Thank you!