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.

[anbu1984]

Directory;

Initial:

LOAD EmpGrp,

    Grade,

    NoofChildren,

    1 As Key

FROM

testing.xlsx

(ooxml, embedded labels, table is Sheet1);

Join(Initial)

Load 1 As Key,Replace(NoofChildren,'>','') As NoofChildrenNew Resident Initial Where Not(IsNum(NoofChildren));

NoConcatenate

Final:

LOAD * Resident Initial Where NoofChildren > NoofChildrenNew;

Drop table Initial;

exit script;

[its_anandrjs]

Hi,

What is your out put in describe and in excel is different

EmpGrp  Grade Noofchildren

HCR       G02   >3 but in file it is G01

Resultant table should be like as

EmpGrp  Grade Noofchildren

HCR       G02   4

HCR       G02   5

Regards

Anand

[Anonymous]

EmpGrp	Grade	Noofchildren
HCC	G01	0
HCR	G02	1
HCM	G03	2
HCC	G01	3
HCR	G02	4
HCM	G03	5
HCC	G01	>3

Resultant table should be like as

EmpGrp	Grade	Noofchildren
HCC	G01	0
HCR	G02	1
HCM	G03	2
HCC	G01	3
HCR	G02	4
HCM	G03	5
HCC	G01	4
HCC	G01	5

Hi ,

Please check this. Thanks for your reply.

[Anonymous]

Hi Anand,

Please check my above reply to anbucheliyan. For example i have mentioned like that. Thanks for your reply.

Help me how to acheive the same.

Thanks,

Nisha loganthan.

[anbu1984]

By what logic you generated only two rows? Is it based on Max Header 3 from other rows?

[its_anandrjs]

Hi,

Then try this way first load for >3 for 4 logic and then for >3 for 5 logic and concatenate this tables also let me know you have only this single condition

Directory;

LOAD Distinct

     EmpGrp,

     Grade,

     NoofChildren,

     If(NoofChildren = '>3',4,NoofChildren) as NoofChildrenNew

FROM

testing.xlsx

(ooxml, embedded labels, table is Sheet1);

Concatenate

LOAD Distinct

      EmpGrp,

     Grade,

     NoofChildren,

     If(NoofChildren = '>3',5,NoofChildren) as NoofChildrenNew

FROM

testing.xlsx

(ooxml, embedded labels, table is Sheet1);

exit script;

Regards

Anand

[Anonymous]

Hi all,

Thanks for your reply. I have two excel files. In one excel file which contains employee details.

For example emp id , Family status, noofchildren. In that maximum noofchildren is 5.

One more data will be like which i attached for sample. In that noofchildren like 0, 1, 2, >1, >2, 4, 5, >3.

If noofchildren is >2,>3,>1>4, that time i want to expand the records till max no of children 5

Thanks,

Nisha

[tresB · Accepted solution]

May be like:

Input:
LOAD EmpGrp,
     Grade,
     PurgeChar(NoofChildren,'<>') As NoofChildren,
     IsNum(NoofChildren) as NumFlag
Inline [
EmpGrp,Grade,NoofChildren
HCC,G01,0
HCR,G02,1
HCM,G03,2
HCC,G01,3
HCR,G02,4
HCM,G03,5
HCC,G01,>3

HCR,G02,>1
]
;

Temp:
Load
Max(NoofChildren) as MaxCh
Resident Input;
Let vMaxChi=Peek('MaxCh');
Drop Table Temp;

Final:

Load
NumFlag,
EmpGrp,
Grade,
NoofChildren+IterNo() as NoofChildren
Resident Input  While IterNo()<=$(vMaxChi)-NoofChildren and NumFlag=0;

Drop Field NumFlag;

[Anonymous]

Thanks Tresco. It is working fine.

Thanks & Regards,

Nisha loganathan