Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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];
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
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
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...
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?
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.
still, for each variable value in the for each statements you're going to read the excel file...
doesn't happen with joins.
Your right, it is a fast and efficient way of duplicating rows.