Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Field Classification

Hi Experts

I am working on one Requirement.

Please look into this. Is that possible in Qlikview?

MY Data

Data_Test.png

Desired Result required in 5 Columns:

Here,

Case1:  11 is Root_1, 14 is Street_1, 25 is Area_1, 22 is Location_1, 66 is City_1

Case2:  44 is Root_1, 48 is Street_1, 98 is Location_1, 76 is City_1

Case3:  69 is Root_1, 94 is City_1

Case4:  88 is Root_1, 92 is Location_1, 77 is City_1

Case5:  42 is Root_1, 19 is Location_1 as well as City_1 (Classify into 2 different Categories - Location & City - if Location & City are Same)

Regards

Eric

4 Replies
Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

You can use the SubField function to retrieve parts of the Hierarchy field:

subfield(Hierarchy, '-',1) as Root_1

subfield(Hierarchy, '-',2) as Street_1

...etc


You'll want to combine this with if-statemtents that check if a value exists in the Street, Area, ..etc fields:

If(Len(Street), subfield(Hierarchy,'-',2) as Street_1


talk is cheap, supply exceeds demand
Not applicable
Author

Can You share it in QVW if Possible.

It's not that easy it's a trick Logic.

Nested Ifs I guess.

Digvijay_Singh

Is there any relation between Street('AA') and Street_1('14' which we know from your explanation below the table only). I mean the codes/dimension relationship you have shared in details are fixed to the shared table or its just a sample.

For example how do we know 94 in 3rd row is not street? I may be asking silly questions to clarify the requirements

sasiparupudi1
Master III
Master III

Hi

See if this helps?

Input:

Load

    *,

Root&'-'&Street&'-'&Area&'-'&Location&'-'&City as Hierarchy1,

subfield(Hierarchy,'-',1) as Root_1,

substringcount(Hierarchy,'-') as HierarchyCount,

if (len(trim(Street))=0,1,0) as StreetMissing,

if (len(trim(Area))=0,1,0) as AreaMissing,

if (len(trim(Location))=0,1,0) as LocationMissing,

if (len(trim(City))=0,1,0) as CityMissing,

4 as Hierarchy1Count;

load *    Inline [

Root,Street,Area,Location,City,Hierarchy

111,AA,BB,CC,DD,11-14-25-22-66

112,SS,,VV,ZZ,44-48-98-76

113,,,,UU,69-94

1114,,,TT,IS,88-92-77

1115,,,RR,RR,42-19

];

NoConcatenate

Link:

Load

*

,

if (HierarchyCount=Hierarchy1Count,subfield(Hierarchy,'-',2),if(StreetMissing=1,'',subfield(Hierarchy,'-',2))) as Street_1,

if (HierarchyCount=Hierarchy1Count,subfield(Hierarchy,'-',3),if(AreaMissing=1,'',subfield(Hierarchy,'-',3))) as Area_1,

if (HierarchyCount=Hierarchy1Count,subfield(Hierarchy,'-',4),if(LocationMissing=1,'',if(HierarchyCount=1,subfield(Hierarchy,'-',2),if(HierarchyCount=2,subfield(Hierarchy,'-',2),if(HierarchyCount=3,subfield(Hierarchy,'-',3)))))) as Location_1,

if (HierarchyCount=Hierarchy1Count,subfield(Hierarchy,'-',5),if(CityMissing=1,'',if(HierarchyCount=1,subfield(Hierarchy,'-',2),if(HierarchyCount=2,subfield(Hierarchy,'-',3),if(HierarchyCount=3,subfield(Hierarchy,'-',4)))))) as City_1

Resident Input;

drop    Table Input;