Loop if(match()) within load

[Anonymous]

Hi, I have the following script part:

CostsTable:

LOAD ProjectId,

          ProjectName,

          If(Match(ProjectCode, '1', '2', '3'), Costsline1, 0) as CostsGroup01,

          If(Match(ProjectCode, '4', '5'), Costsline2, 0) as CostsGroup02,

          ...

RESIDENT CostsTable_tmp;

Now I want to create more than one new field on every same If(Match())-clause. I could repeat the code but I want to use a loop or an expanded statement which can contain more than one 'load as' without getting cluttered in nested if's. How can I do this? Regards, Sander

[its_anandrjs]

Hi,

Use loop statement to load the data like counter loop

For CTR = 1 to 12

     Load Statments

Next

Let me know about this.

HTH

Regards

Anand

[Anonymous]

Hi Anand,

For/next gives me separate tables to be joined. Ok, but is there a way to keep this in one load?

And for instance combine in one line:

If(Match(ProjectCode, '1', '2', '3'), Costsline1, 0) as CostsGroup01,

If(Match(ProjectCode, '1', '2', '3'), Revenues1, 0) as RevenuesGroup01,

Regards, Sander

[#4]

HI Sander,

u can try something like this:

set list_project= '1','2','3','4','5',........;

load

if(substringcount($(list_project),ProjectCode)>0,Costsline1,0, as CostsGroup01,

if(substringcount($(list_project),Revenuse)>0,Revenues1, as RevenuesGroup01,

..

resident...

I hope this help you.

Stefano

[#5]

Hi,

you should use another mapping excel rather than creating dynamically.

CostsTable:

LOAD ProjectId,

          ProjectName, 

ProjectCode        ...

RESIDENT CostsTable_tmp;

Grouping:

Load ProjectCode, CostGroup from table CostGrp.xls;

And in the XLS you maintain the groupings

Projectid, CostGrp

1, CostsGroup

2, CostsGroup

4, RevenueGrp

5,RevenueGrp

Hope this should help you.

Cheers.

[Anonymous]

Hi, thanks for your tips! I'm now working with variables like this:

SET vFieldName_1='CostsLine1'

SET vFieldPrefix_1='Costs'

SET vFieldName_2='Revenues1'

SET vFieldPrefix_2='Revenues'

etc...

FOR i=1 to ...;

LET vFieldName="vFieldName_$(i)";

LET vFieldPrefix="vFieldPrefix_$(i)";

LEFT JOIN

LOAD IdField,

          If(Match(ProjectCode, '1', '2', '3'), $(vFieldName), 0) as $(vFieldPrefix)_hardcoded_identifier;

...

RESIDENT ...

NEXT i;

Regards, Sander