topic Re: Hierarchy problem in QlikView

Hierarchy problem

Tue, 22 Mar 2016 19:53:00 GMT

I have two files but with different hierarchy.Pls find the below example

File1 File2

Head Dir Mgr Head Dir Mgr

Nikhil Sam Tom Nikhil Sam Tom

Nikhil Sam Brady Nikhil Sam Brady

- - - Nikhil Sam Reece

Paul Mel Lee Paul Mel David

Output:

Head Dir Mgr Diff

Nikhil Sam Tom No diff

Nikhil Sam Brady No diff

Nikhil Sam Reece Diff

Paul Mel Lee Diff

Paul Mel David Diff

Please guide me on this...

Re: Hierarchy problem

Chanty4u — Wed, 23 Mar 2016 05:08:26 GMT

chk dis

Unbalanced, n-level hierarchies

Re: Hierarchy problem

avinashelite — Wed, 23 Mar 2016 05:43:12 GMT

Try like this :

File1:

LOAD Head&'-'&Dir&'-'&Mgr as file1_key

from

file1;

File2:

LOAD Head&'-'&Dir&'-'&Mgr as file2_key

from

file2;

Temp:

LOAD Head&'-'&Dir&'-'&Mgr as key,

Head,

Dir,

Mgr

from

file1;

concatenate

LOAD Head&'-'&Dir&'-'&Mgr as key,

Head,

Dir,

Mgr

from

file2;

Output:

LOAD

Head,

Dir,

Mgr,

if(Exists(file1_key,key)=-1 and Exists(file2_key,key)=-1,'No diff','diff') as Diff

resident

Temp;

drop table Temp;

Re: Hierarchy problem

settu_periasamy — Wed, 23 Mar 2016 05:46:20 GMT

Hi,

May be like this..

T1:
LOAD Head&Dir&Mgr as Key,*;
LOAD * INLINE [
Head, Dir, Mgr
Nikhil, Sam, Tom
Nikhil, Sam, Brady
-,-,-
Paul, Mel, Lee
];

Left Join(T1)
T2:
LOAD Head&Dir&Mgr as Key,*,'NoDiff' as Diff;
LOAD * INLINE [
Head, Dir, Mgr
Nikhil, Sam, Tom
Nikhil, Sam, Brady
Nikhil, Sam, Reece
Paul, Mel, David
];

Concatenate(T1)

LOAD *,'Diff' as Diff Where not Exists(Key);
LOAD Head&Dir&Mgr as Key,*;
LOAD * INLINE [
Head, Dir, Mgr
Nikhil, Sam, Tom
Nikhil, Sam, Brady
Nikhil, Sam, Reece
Paul, Mel, David
];

NoConcatenate
Final:
LOAD if(IsNull(Diff) or Diff='Diff','Diff','NoDiff') as Diff_New,* Resident T1 Where Len(Key)>3;

DROP Field Diff;
DROP Table T1;

Re: Hierarchy problem

Digvijay_Singh — Wed, 23 Mar 2016 05:57:47 GMT

file1:

Load *,Autonumber(Head&Dir&Mgr,'Counter') as SNO inline [

Head , Dir , Mgr

Nikhil , Sam , Tom

Nikhil , Sam , Brady

'' , '' , ''

Paul , Mel , Lee ];

Load *, Autonumber(Head&Dir&Mgr,'Counter') as SNO inline [

Head , Dir , Mgr

Nikhil , Sam , Tom

Nikhil , Sam , Brady

Nikhil , Sam , Reece

Paul , Mel , David ];

File3:

Load *,if (Count(SNO)>1,'No Diff','Diff') as Diff

Resident file1

Where len(Head)>0 and len(Dir)>0 and len(Mgr) >0

Group By SNO,Head,Dir,Mgr;

Drop table file1;

Drop Field SNO;

Re: Hierarchy problem

Tue, 29 Mar 2016 19:04:25 GMT

Thanks Digvijay, Its working fine.

now i would like to see now is 1 object that has a list of the differences then if an item is selected from the mismatch list - two objects display

1) the data from the File1

2) the data from the File2

Please help me asap

Thanks