Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
HonestToad
Partner - Contributor II
Partner - Contributor II

Do... Loop that uses the loop variable in a string

I currently have a very repetitive load string that I am sure could be simplified with a Do... Loop, but I am not getting any results.  At the moment I do the following:

TeamHieracy:
LOAD Distinct
    SubField(SubField("Primary Organization",'\',4),' ', -1) as GroupID,
    SubField(SubField("Primary Organization",'\',-1),' ', -1) as PrimaryID    
FROM [lib://team.qvd]
(qvd) where SubField(SubField("Primary Organization",'\',12),' ', -1) = subfield("Primary Organization", ' ', -1);

Concatenate(TeamHieracy)
LOAD Distinct
    SubField(SubField("Primary Organization",'\',4),' ', -1) as GroupID,
    SubField(SubField("Primary Organization",'\',-1),' ', -1) as PrimaryID    
FROM [lib://team.qvd]
(qvd) where SubField(SubField("Primary Organization",'\',11),' ', -1) = subfield("Primary Organization", ' ', -1);

Concatenate(TeamHieracy)
LOAD Distinct
    SubField(SubField("Primary Organization",'\',4),' ', -1) as GroupID,
    SubField(SubField("Primary Organization",'\',-1),' ', -1) as PrimaryID    
FROM [lib://team.qvd]
(qvd) where SubField(SubField("Primary Organization",'\',10),' ', -1) = subfield("Primary Organization", ' ', -1);


The only thing that is different in each line is a number in the where clause.  I tried writing something like this:

TeamHieracy2:
Set a=12;
Do while a<0

LOAD Distinct
    SubField(SubField("Primary Organization",'\',4),' ', -1) as GroupID,
    SubField(SubField("Primary Organization",'\',-1),' ', -1) as PrimaryID    
FROM [lib://team.qvd]
(qvd) where SubField(SubField("Primary Organization",'\',$(a)),' ', -1) = subfield("Primary Organization", ' ', -1);

Let a=12-1;
Loop


Unfortunately this doesn't return anything.  I would welcome some help on getting this loop working as it would really tidy my code

Kind regards

Stuart




Labels (5)
1 Reply
treysmithdev
Partner Ambassador
Partner Ambassador

Not sure if a typo or your actual code, but the while loop you wrote won't do an iteration because you set your condition to a<0. a starts at 12, so it already succeeded the condition. To make it work, just change it to 'a>=0'.

 

Another option is you could use a While loop instead. Not sure which would be faster from a performance perspective.

 

TeamHiearchy2:
LOAD DISTINCT
    GroupID,
    PrimaryID
Where
    WhereMatch = 1;
LOAD 
    SubField(SubField("Primary Organization",'\',4),' ', -1) as GroupID,
    SubField(SubField("Primary Organization",'\',-1),' ', -1) as PrimaryID,
    If(SubField(SubField("Primary Organization",'\',IterNo()-1,' ', -1) = subfield("Primary Organization", ' ', -1),1) as WhereMatch
FROM 
    [lib://team.qvd](qvd) 
While 
    (IterNo()-1 <= 12) ;

 

 

Blog: WhereClause   Twitter: @treysmithdev