Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
kdmarkee221
Contributor III
Contributor III

Loop statements in Qlikview

I'm struggling with loops in my script.  Thanks for your help.

How do I loop through my CrostableWork_temp02 (which is an odd but correct table) table below 60 times (as represented by variable t$) but end up with a single table when it is done?

LOAD
RollUp_ID,
Count(MinuteColName) as [Times with $(t)]
Resident CrostableWork_temp02
Where Match(MinuteColValue, $(t))
Group by RollUp_ID

 

So a sample from CrostableWork_temp02 might be:

kdmarkee221_0-1692032777384.png

Which I want it to produce this:

kdmarkee221_1-1692032820894.png

 

Labels (1)
1 Solution

Accepted Solutions
edwin
Master II
Master II

try this:

data:
load * inline [
RollID, ColNameCount, ColValue
1, 2, 1
1, 3, 2
2, 1, 4
3,2,5
];


for i=1 to 5
	left join (data)
    load RollID, ColNameCount as [Times with $(i)]
    Resident data
    where ColValue=$(i);
next i

which is a shortcut once youve aggreagated the counts.  here is the result:

edwin_0-1692038056486.png

 



View solution in original post

2 Replies
edwin
Master II
Master II

try this:

data:
load * inline [
RollID, ColNameCount, ColValue
1, 2, 1
1, 3, 2
2, 1, 4
3,2,5
];


for i=1 to 5
	left join (data)
    load RollID, ColNameCount as [Times with $(i)]
    Resident data
    where ColValue=$(i);
next i

which is a shortcut once youve aggreagated the counts.  here is the result:

edwin_0-1692038056486.png

 



kdmarkee221
Contributor III
Contributor III
Author

Thank you, the left join was a great idea and seems to have done the trick.  I just did some clean up on your idea by using my logic, that is , if using your concept of an inline load, I built my inline table with only the RollUpID, then the left join in the for/next statement did the rest of the work of counting per RollUpID.