topic Re: Field Classification in QlikView

Field Classification

Fri, 18 Sep 2015 05:44:01 GMT

Hi Experts

I am working on one Requirement.

Please look into this. Is that possible in Qlikview?

MY Data

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

Re: Field Classification

Gysbert_Wassenaar — Fri, 18 Sep 2015 05:47:30 GMT

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

Re: Field Classification

Fri, 18 Sep 2015 05:51:55 GMT

Can You share it in QVW if Possible.

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

Nested Ifs I guess.

Re: Field Classification

Digvijay_Singh — Fri, 18 Sep 2015 06:14:06 GMT

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

Re: Field Classification

sasiparupudi1 — Fri, 18 Sep 2015 08:05:22 GMT

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;