Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
melissapluke
Partner - Creator
Partner - Creator

Script Looping or ways to make more efficient?

Hi all,

I have the following script. I need to load a 'Code' and a 'Rate' from a table. From there, I need to associate that code with both 'Group 3' and 'Group 4' Group, and a 'Location A' and 'Location B' Flag. The script below works fine, but I end up loading the same data 4 times. I do this same type of thing repeatedly throughout my script, so I was wondering if there was more efficient way to do this with a loop or variable or something of the sort. Any help would be appreciated. Thanks!

LOAD
'Group 3' as "Group Flag",
text("Code") as Procedure,
'Location A' as "Flag",
Rate
FROM [lib://table.xlsx];
LOAD
'Group 3' as "Group Flag",
text("Code") as Procedure,
'Location B' as "Flag",
Rate
FROM [lib://table.xlsx];

LOAD
'Group 4' as "Group Flag",
text("Code") as Procedure,
'Location A' as "Flag",
Rate
FROM [lib://table.xlsx];
LOAD
'Group 4' as "Group Flag",
text("Code") as Procedure,
'Location B' as "Flag",
Rate
FROM [lib://table.xlsx];

1 Solution

Accepted Solutions
Vegar
MVP
MVP

For each _group in 'Group 3', 'Group 4'

  For each _location in 'Location A' , 'Location B' 

   LOAD

     '$(_group)' as "Group Flag",

     text("Code") as Procedure,

     '$(_location)' as "Flag",

     Rate

  FROM [lib://table.xlsx];

  Next

Next

View solution in original post

6 Replies
Vegar
MVP
MVP

For each _group in 'Group 3', 'Group 4'

  For each _location in 'Location A' , 'Location B' 

   LOAD

     '$(_group)' as "Group Flag",

     text("Code") as Procedure,

     '$(_location)' as "Flag",

     Rate

  FROM [lib://table.xlsx];

  Next

Next

mikaelsc
Specialist
Specialist

not sure to get the purpose of this but:

Data: 

LOAD
text("Code") as Procedure,
Rate
FROM [lib://table.xlsx];

left join(Data)

load * inline [

GroupFlag

Group3

Group4

];

left join(Data)

load * inline [

Flag

Location A

Location B

];

 

replace the inlines with whatever source you want... 

melissapluke
Partner - Creator
Partner - Creator
Author

Thank you, this works great. I'm assuming if I have more "For each" statements in my real life situation, I'll have to add an additional "Next" for every "for each" statement?

Vegar
MVP
MVP

Glad i could help. Yes you are correct, the next is connected to the for each statement. If you add an additional for each ... then you would need an additional next.

mikaelsc
Specialist
Specialist

still, for each variable value in the for each statements you're going to read the excel file... 

doesn't happen with joins.

Vegar
MVP
MVP

Your right, it is a fast and efficient way of duplicating rows.