Need Help on Hierarchy

[agni_gold]

Hi All ,

I have a situation , i have generated Entity hierarchy from Hierarchy function and getting attached Columns ,

Now i don't want rest of the columns Just want

Corporate & Segment & Sub Segment & Base Entity .

Here Base entities are those entities which are coming at the last of each columns means whose frequency is 1.

I have tried a way

Entity:

LOAD Corporate,

     Segment,

     SubSegment,

     [Level3 Entity],

     [Level4 Entity],

     [Level5 Entity],

     [Level6 Entity]

FROM

(ooxml, embedded labels, table is Sheet1);

Required:

Load Corporate,

     Segment,

     SubSegment,

     Corporate&'>'&Segment&'>'&SubSegment   AS Hierarchy,

     SubSegment   AS Base,

     count(SubSegment)  AS Count

Resident Entity where Len(SubSegment)<>0

Group by Corporate,Segment,SubSegment;

NoConcatenate

A:

Load Corporate,Segment,if(Count<>1,Base)  AS SubSegment,if(Count=1,Base) AS Base , Corporate&'>'&Segment&'>'&if(Count<>1,Base)&'>'&if(Count=1,Base) AS Hierarchy

Resident Required ;

Drop Tables Entity,Required;

Exit Script;

But this is quite large script , this is only for one column .

Is any option in the QlikView Hierarchy function which can only generate required columns from Hierarchy function.

Please help

[hic]

The Hierarchy prefix needs an adjacent nodes table (each node on its own record, with a reference to the parent) but what you have is an Expanded nodes table. So you cannot use the hierarchy prefix straight off. You could transform the source table into an adjacent nodes table, but it would be easier if you already had one.

Further, you could get rid of unwanted fields by using a Drop Fields statement.

HIC

[Digvijay_Singh]

Try this -

It gives the last value as base, only problem is if SubSegment is empty then it appears empty but correctly takes Segment as Base.

Temp:

LOAD Corporate,

    Segment,

    SubSegment,

    [Level3 Entity],

    [Level4 Entity],

    [Level5 Entity],

    [Level6 Entity]

FROM

(ooxml, embedded labels, table is Sheet1);

Output:

Load

  Corporate,

    Segment,

    SubSegment,

    If(len(SubSegment)<1,Segment,if(len([Level3 Entity])<1,SubSegment,If(len([Level4 Entity])<1,[Level3 Entity],If(len([Level5 Entity])<1,[Level4 Entity],If(len([Level6 Entity])<1,[Level5 Entity],[Level6 Entity]))))) as Base

Resident Temp ;

Drop Table Temp;

[agni_gold]

When i am making Hierarchy from your script the tree vie is not comming in right way AWPElims, GenieAdj, TerexChonghou is base entity , so they should come under TotTerexAerials.

[Digvijay_Singh]

Hi,

Problem is with Subsegment field, as you mentioned in the beginning -

'Now i don't want rest of the columns Just want

Corporate & Segment & Sub Segment & Base Entity'

I observed that SubSegment is empty in few rows, if you add Segment value in Base then Base and Segment will be same and will have empty level as Subsegment.

When all levels after Subsegments are empty, then do you want Subsegment value to be copied in base or no base value.

I think if we fill base value only when Level 3 or higher levels have values then it may work. Still thinking

[Digvijay_Singh]

Now it looks better - Try below script -

Temp:

LOAD Corporate,

     Segment,

     SubSegment,

     [Level3 Entity],

     [Level4 Entity],

     [Level5 Entity],

     [Level6 Entity]

FROM

(ooxml, embedded labels, table is Sheet1);

Output:

Load *, Corporate & '/' & Segment & if(len(SubSegment)>0,'/' & SubSegment) & if(len(Base)>0,'/' & Base) as Hier;

Load

  Corporate,

     Segment,

     SubSegment,

     If(len([Level4 Entity])<1,[Level3 Entity],If(len([Level5 Entity])<1,[Level4 Entity],If(len([Level6 Entity])<1,[Level5 Entity],[Level6 Entity]))) as Base

Resident Temp ;

Drop Table Temp;

[agni_gold]

Hi Digvijay,

Thanks for your valuable time , and great help

This is almost solving my problem , but i am not able to select corporate . Using your script i have introduce one more column frequency here .

Please see the screenshot

When i am selecting Segment frequency is 2 , for sub segment is 3 ,and site is 4 ,

Here i want Corporate is selectable and for corporate frequency should come 1 ,

And in SubSegment field i am getting sites ,and in site listbox , i am getting some subsegments.

Please help on this .

Thanks

[Digvijay_Singh]

Hi,

Can you share how you have derived the frequency column. I couldn't understand, may be my bad.

Also couldn't make out from below -

When i am selecting Segment frequency is 2 , for sub segment is 3 ,and site is 4 ,

Here i want Corporate is selectable and for corporate frequency should come 1 ,

And in SubSegment field i am getting sites ,and in site listbox , i am getting some subsegments.

May be once I understand the Frequency column logic, I will understand rest of the things.

Thanks,

Digvijay

[Digvijay_Singh]

Also wanted to know, What is 'Site' here? is it new column 'Base' or something else?

[Digvijay_Singh]

Could you resolve your final hurdle on this? Was curious to know Site and Frequency details..