topic Loop if(match()) within load in QlikView

Loop if(match()) within load

Anonymous — Mon, 12 Sep 2011 08:30:55 GMT

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

Loop if(match()) within load

its_anandrjs — Mon, 12 Sep 2011 09:37:19 GMT

Hi,

Use loop statement to load the data like counter loop

For CTR = 1 to 12

Load Statments

Let me know about this.

HTH

Regards

Anand

Loop if(match()) within load

Anonymous — Mon, 12 Sep 2011 11:42:24 GMT

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

Loop if(match()) within load

Tue, 13 Sep 2011 05:56:40 GMT

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

Re: Loop if(match()) within load

Tue, 13 Sep 2011 08:31:47 GMT

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.

Loop if(match()) within load

Anonymous — Thu, 15 Sep 2011 21:05:05 GMT

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