Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

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
Highlighted
Partner
Partner

Re: Script Looping or ways to make more efficient?

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

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

6 Replies
Highlighted
Partner
Partner

Re: Script Looping or ways to make more efficient?

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

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

Highlighted
Creator III
Creator III

Re: Script Looping or ways to make more efficient?

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... 

Highlighted
Partner
Partner

Re: Script Looping or ways to make more efficient?

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?

Highlighted
Partner
Partner

Re: Script Looping or ways to make more efficient?

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.

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Highlighted
Creator III
Creator III

Re: Script Looping or ways to make more efficient?

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

doesn't happen with joins.

Highlighted
Partner
Partner

Re: Script Looping or ways to make more efficient?

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

Plees ekskuse my Swenglish and or Norweglish spelling misstakes