Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ananyaghosh
Creator III
Creator III

Need hierarchies for the attached table

Hi,

I want to create hierarchies for the attached excel sheet tables. Please provide me steps how to do that in Qlikview? after creating hierarchies I want to show it in list box.

I have created script for Hierarchies. But it does not work. Please help me.

I have attached the excel file also.

1 Solution

Accepted Solutions
hic
Former Employee
Former Employee

... but if you want it just to create the path for the tree-view list box, you should do what simone.spanio‌ suggests: concatenate all nodes and then use Hierarchy on the resulting table:

tmpNodes:
LOAD
'Country' &
CountryID as NodeID,
CountryName as Name,
'Country'
as NodeType,
Null() as ParentID
FROM Country.xlsx (ooxml, embedded labels, table is Sheet1);

Concatenate LOAD
'Region' &
RegionID as NodeID,
RegionName as Name,
'Region'
as NodeType,
'Country' &
CountryID as ParentID
FROM Region.xlsx (ooxml, embedded labels, table is Sheet1);

Concatenate LOAD
'State' &
StateID as NodeID,
StateName as Name,
StateShortDesc as Description,
'State'
as NodeType,
'Region' &
RegionID  as ParentID
FROM State.xlsx (ooxml, embedded labels, table is Sheet1);

Concatenate LOAD
'City' &
CityID as NodeID,
CityName as Name,
'City'
as NodeType,
'State' &
StateID as ParentID
FROM City.xlsx (ooxml, embedded labels, table is Sheet1);

Nodes:
Hierarchy (NodeID, ParentID, Name, ParentName, Name, Path, '/', Depth)
Load * Resident tmpNodes ;
Drop Table tmpNodes;

HIC

View solution in original post

6 Replies
simospa
Partner - Specialist
Partner - Specialist

Hi,

can you attach the .xls file? You attached only qvw.

S.

simospa
Partner - Specialist
Partner - Specialist

You could begin with concatenate all data in a unique table, then store it in a qvd that will contain a unique structure.

Then you can import it using hierarchy tool.

But try to start from a flat table with all data together.

S.

ananyaghosh
Creator III
Creator III
Author

Hi,

LOAD CountryID,
    
CountryName
FROM
[\\Client\D$\304091\project\data\Dummy data\Country.xlsx]
(
ooxml, embedded labels, table is Sheet1);
inner join
LOAD RegionID,
    
RegionName,
    
CountryID
FROM
[\\Client\D$\304091\project\data\Dummy data\Region.xlsx]
(
ooxml, embedded labels, table is Sheet1);
inner join
LOAD StateID,
    
StateName,
    
StateShortDesc,
    
RegionID FROM
[\\Client\D$\304091\project\data\Dummy data\State.xlsx]
(
ooxml, embedded labels, table is Sheet1);
inner join
LOAD [City ID],
    
[City Name],
    
[State ID] as StateID,
    
latitude,
    
longitude,
    
zip_code
FROM
[\\Client\D$\304091\project\data\Dummy data\CityDeatils.xls]
(
biff, embedded labels, table is [city-4$]);

ITEM:

LOAD Distinct CountryName as VALUE, CountryName & '-CountryName' as NODE_ID, [CountryID] & '-CountryID' as PARENT_NODE_ID resident Location;
LOAD Distinct RegionName as VALUE, [RegionName] & '-Region' as NODE_ID, [CountryID] & '-CountryID' as PARENT_NODE_ID resident Location;
LOAD Distinct StateName as VALUE, [StateName] & '-State' as NODE_ID, [RegionID] & '-RegionID' as PARENT_NODE_ID resident Location;
LOAD Distinct [City Name] as VALUE, [City Name] & '-City' as NODE_ID, [StateID] & '-StateID' as PARENT_NODE_ID resident Location;

HIERARCHY_TABLE:
Hierarchy(NODE_ID_H,PARENT_NODE_ID_H, ID, PARENT_NAME, NAME, NAME_FOR_TREE) load
NODE_ID,
NODE_ID as NODE_ID_H,
PARENT_NODE_ID as PARENT_NODE_ID_H,
VALUE as ID,
VALUE as NAME
resident ITEM;

and use NAME_FOR_TREE on a list box. But it does not show hierarchically. Please help

hic
Former Employee
Former Employee

Not sure what you want to do... The data is already in a structured way, with each level properly named (City, State, Region, Country). And the facts are already per City.

So this is a balanced hierarchy - it is not unbalanced - so you don't need the Hierarchy prefix. Just load the data as it is, and it will work.

HIC

hic
Former Employee
Former Employee

... but if you want it just to create the path for the tree-view list box, you should do what simone.spanio‌ suggests: concatenate all nodes and then use Hierarchy on the resulting table:

tmpNodes:
LOAD
'Country' &
CountryID as NodeID,
CountryName as Name,
'Country'
as NodeType,
Null() as ParentID
FROM Country.xlsx (ooxml, embedded labels, table is Sheet1);

Concatenate LOAD
'Region' &
RegionID as NodeID,
RegionName as Name,
'Region'
as NodeType,
'Country' &
CountryID as ParentID
FROM Region.xlsx (ooxml, embedded labels, table is Sheet1);

Concatenate LOAD
'State' &
StateID as NodeID,
StateName as Name,
StateShortDesc as Description,
'State'
as NodeType,
'Region' &
RegionID  as ParentID
FROM State.xlsx (ooxml, embedded labels, table is Sheet1);

Concatenate LOAD
'City' &
CityID as NodeID,
CityName as Name,
'City'
as NodeType,
'State' &
StateID as ParentID
FROM City.xlsx (ooxml, embedded labels, table is Sheet1);

Nodes:
Hierarchy (NodeID, ParentID, Name, ParentName, Name, Path, '/', Depth)
Load * Resident tmpNodes ;
Drop Table tmpNodes;

HIC

ananyaghosh
Creator III
Creator III
Author

Hello Sir,

I have used a list box and use 'Path' as a field name of that list box with tree view option. Now the list box work properly.

Thanks,

Sandip

Hello SIr