Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Partner
Partner

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 (7)
1 Reply
Partner
Partner

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

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