Script Looping or ways to make more efficient?

[melissapluke]

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];

[Vegar · Accepted solution]

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]

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]

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]

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]

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

doesn't happen with joins.

[Vegar]

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