Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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;
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.
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
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;
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
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
Also wanted to know, What is 'Site' here? is it new column 'Base' or something else?
Could you resolve your final hurdle on this? Was curious to know Site and Frequency details..