Need hierarchies for the attached table

[ananyaghosh]

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.

[simospa]

Hi,

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

S.

[simospa]

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]

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]

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 · Accepted solution]

... 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]

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