Iterno()

[Anonymous]

Hi all,

I am having data in excel like

EmpGrp  Grade Noofchildren

HCR       G02   >3

In this case, if noofchildren>3 means, it should be as 4, 5

Resultant table should be like as

EmpGrp  Grade Noofchildren

HCR       G02   4

HCR       G02   5

Thanks in Advance, Nisha loganathan.

[#2]

Wouldn't this mean that you would have one extra (unwanted) entry for each < or > entry, given how you calculate what the iterno() is based against?
As an example HCR, GR02 would have an entry with NoofChildren as 1 despite it being greater than 1.

An easy way around it might be to do a wildmatch if statement and then add +1 to the end of your purgechar statement?

[its_anandrjs]

Hi Nisha,

You are right then try to load it dynamically upto 5 max value and need to regenerate that fields also for this first find the max values and then

Tab:

LOAD

     EmpGrp,

     Grade,

     NoofChildren

FROM

testing.xlsx

(ooxml, embedded labels, table is Sheet1);

Max:

Load

Max(NoofChildren) as MaxChildVal

Resident Tab;

Let vMax=Peek('MaxChildVal',0,'Max');

NoConcatenate

TabFindGreaterSym:

LOAD

     EmpGrp,

     Grade,

     NoofChildren

Resident Tab Where Left(NoofChildren,1) = '>';

Drop Table Tab;

Final:

NoConcatenate

LOAD

     EmpGrp,

     Grade,

     NoofChildren,

     Right(NoofChildren,Len(NoofChildren)-1) + IterNo() as NoofChildrenNew

Resident TabFindGreaterSym  While IterNo() <= $(vMax)- Right(NoofChildren,Len(NoofChildren)-1);

DROP Table TabFindGreaterSym;

Concatenate

LOAD

      EmpGrp,

     Grade,

     NoofChildren,

     NoofChildren as NoofChildrenNew

FROM

testing.xlsx

(ooxml, embedded labels, table is Sheet1) Where not Left(NoofChildren,1) = '>';

Little bit change in the Tresesco logic

Regards

Anand

[Anonymous]

Hi,

Thank yo so much. Helped me a lot.

Thanks,

Nisha loganathan