topic Re: Need Help on Hierarchy in QlikView

Need Help on Hierarchy

agni_gold — Tue, 06 Oct 2015 11:37:29 GMT

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

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

Re: Need Help on Hierarchy

hic — Tue, 06 Oct 2015 11:53:13 GMT

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

Re: Need Help on Hierarchy

Digvijay_Singh — Tue, 06 Oct 2015 12:38:52 GMT

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;

Re: Need Help on Hierarchy

agni_gold — Tue, 06 Oct 2015 17:09:49 GMT

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.

Re: Need Help on Hierarchy

Digvijay_Singh — Tue, 06 Oct 2015 19:48:17 GMT

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

Re: Need Help on Hierarchy

Digvijay_Singh — Tue, 06 Oct 2015 19:59:49 GMT

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;

Re: Need Help on Hierarchy

agni_gold — Wed, 07 Oct 2015 00:40:43 GMT

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

Re: Need Help on Hierarchy

Digvijay_Singh — Wed, 07 Oct 2015 03:09:24 GMT

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

Re: Need Help on Hierarchy

Digvijay_Singh — Wed, 07 Oct 2015 03:14:19 GMT

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

Re: Need Help on Hierarchy

Digvijay_Singh — Fri, 09 Oct 2015 07:36:34 GMT

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