loop in script to run the same code for 2 data sources

[kmstephenson]

Hi All-

I have multiple of the same structured datasets to pull in for 2 different data sources. I would like to be able to use a loop to run through the code for both data sources on the same reload instead of having to run my script twice.

Currently, I have the following at the beginning of my script:

SET vSource="Source1";

SET vSource="Source2";

When I run my script, I have to comment out one of the vSource and run the script twice, once for each vSource to run through multiple tabs that include code such as the following.

$(vSource)PopChar:
LOAD *
FROM
[$(vdirT1Source)$(vSource)\$(vSource)_pop_char_$(vDtSuffix).csv]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

STORE '$(vSource)PopChar' INTO [$(vdirT1HipViews)$(vSource)\T1_$(vSource)PopChar.qvd];
DROP TABLE $(vSource)PopChar;

I have each table that I pull in for each data source, with the exception of two tables on the same tab that I only have for the first source. I use IF '$(vSource)'='Source1' THEN

END IF command around code on that tab to only run for Source1.

Should I use FOR EACH NEXT command or another type of LOOP? I have not been successful in the loop code running and not sure what I am missing.

Thanks!

[anbu1984]

You can use For Each Next

For Each vSource in Source1 Source2

$(vSource)PopChar:

LOAD *
FROM
[$(vdirT1Source)$(vSource)\$(vSource)_pop_char_$(vDtSuffix).csv]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

STORE '$(vSource)PopChar' INTO [$(vdirT1HipViews)$(vSource)\T1_$(vSource)PopChar.qvd];
DROP TABLE $(vSource)PopChar;

Next vSource

[JonnyPoole]

Here is an example with 2 spreadsheet sources

let vSource1='';

let vSource2='';

for x = 1 to 2

let vSourceX='vSource'& $(x);

LOAD Name,

     Values

FROM

   $($(vSourceX))

(ooxml, embedded labels, table is Sheet1);

next x;

[nagaiank · Accepted solution]

You may use the following script (same as the one by Anbu Chelian with slight changes):

For Each vSource in 'Source1', 'Source2'

$(vSource)PopChar:

LOAD *
FROM
[$(vdirT1Source)$(vSource)\$(vSource)_pop_char_$(vDtSuffix).csv]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

STORE '$(vSource)PopChar' INTO [$(vdirT1HipViews)$(vSource)\T1_$(vSource)PopChar.qvd] (qvd);
DROP TABLE $(vSource)PopChar;

Next vSource

[kmstephenson]

Hi Jonathan,

Thanks for the quick response. So I used Source1 and Source2 thinking it made this more clear and didn't realize that having numbers in the source made this easier. My real sources do not have numbers in the naming convention. Source1 is really Medicare and Source2 is Commercial.

When I try

LET vSource1="Medicare";
LET vSource2="Commercial";
FOR x = 1 TO 2

LET vSourceX='vSource'& $(x);

$(vSourceX)PopChar:
LOAD *
FROM
[$(vdirT1Source)$(vSourceX)\$(vSourceX)_pop_char_$(vDtSuffix).csv]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

STORE '$(vSourceX)PopChar' INTO [$(vdirT1HipViews)$(vSourceX)\T1_$(vSourceX)PopChar.qvd];
DROP TABLE $(vSourceX)PopChar;

NEXT x;

It resolves to vSource1 in my code instead of Medicare... what do I need to do to get this to resolve once more to my actual source names?

Thank you!

[JonnyPoole]

Hi there - so because there are 2 levels of assembly (the first is to create a string with the right variable name and the second is to invoke the value of that variable) ... you'll notice that in my example i double wrapped my variable with 2 sets of $() expansion:   $($(vSourcex))      wherever i am invoking the variable value

Try that next and let us know what happens.

[kmstephenson]

Oh, I missed that! That should work as well. Is there any benefit to using this route versus the FOR EACH NEXT method NagaianK provided above? Is one more efficient than the other? Both work great, thanks!

[JonnyPoole]

no performance differences that i know of.  its nice to have 2 solutions in case one is slower but i don't see that here.

I tend to like less verbose code personally but ultimately it depends on the readability.  the for next is slicker, the for (my example) is longer with more steps. i'd suggest to just go with the solution that looks easier to read and manage