Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

Looping and sorting

Hi all,

This  1st table is the Data table I have, with master and sub different, in the 2nd table the sub items having SID = MID s of their respective Master items re arranged below them. I want to arrange the sub items below their master items with the help of MIDs. How can I do it?  Urgent.

note- each sub item's SID is equal to its respective master item's MID.

Table1:                                                                                                                                            :

RelationshipMIDSID
Master nil100
Master nil600
Master nil200
sub 1001291
sub 2001292
sub 1001293
Master nil400
sub 6001298

 

Table 2:

RelationshipMIDSID
Master nil100
sub 1001291
sub 1001293
Master nil200
sub 2001292
Master nil400
Master nil600
sub 6001298
2 Replies
Highlighted
Specialist III
Specialist III

One solution is:

tab1:
LOAD * INLINE [
    Relationship, MID, SID
    Master , nil, 100
    Master , nil, 600
    Master , nil, 200
    sub , 100, 1291
    sub , 200, 1292
    sub , 100, 1293
    Master , nil, 400
    sub , 600, 1298
];

mapMaster:
Mapping
LOAD SID, Relationship&'/'&SID
Resident tab1;

tab2:
LOAD *, If(Relationship='Master',
			Relationship&'/'&SID,
			ApplyMap('mapMaster',MID)) As %Sort
Resident tab1;

Drop Table tab1;

commQV04.PNG

Highlighted
Specialist III
Specialist III

tab1:
LOAD * INLINE [
    Relationship, MID, SID
    Master , nil, 100
    Master , nil, 600
    Master , nil, 200
    sub , 100, 1291
    sub , 200, 1292
    sub , 100, 1293
    Master , nil, 400
    sub , 600, 1298
];

mapMaster:
Mapping
LOAD SID, Relationship&'/'&SID
Resident tab1
Where Relationship='Master';

tab2:
LOAD *, If(Relationship='Master',
			Relationship&'/'&SID,
			ApplyMap('mapMaster',MID)) As %Sort
Resident tab1;

Drop Table tab1;