topic Re: Re: Iterno() in QlikView

Iterno()

Anonymous — Tue, 02 Dec 2014 09:58:27 GMT

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.

Re: Iterno()

anbu1984 — Tue, 02 Dec 2014 10:14:57 GMT

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;

Re: Iterno()

its_anandrjs — Tue, 02 Dec 2014 10:36:34 GMT

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

Re: Re: Iterno()

Anonymous — Tue, 02 Dec 2014 10:42:04 GMT

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.

Re: Iterno()

Anonymous — Tue, 02 Dec 2014 10:48:46 GMT

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.

Re: Iterno()

anbu1984 — Tue, 02 Dec 2014 10:49:23 GMT

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

Re: Re: Iterno()

its_anandrjs — Tue, 02 Dec 2014 10:50:31 GMT

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

Re: Re: Iterno()

Anonymous — Tue, 02 Dec 2014 11:02:46 GMT

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

Re: Iterno()

tresB — Tue, 02 Dec 2014 11:13:44 GMT

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;

Re: Re: Iterno()

Anonymous — Tue, 02 Dec 2014 12:25:36 GMT

Thanks Tresco. It is working fine.

Thanks & Regards,

Nisha loganathan

Re: Iterno()

Tue, 02 Dec 2014 12:28:26 GMT

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?

Re: Iterno()

its_anandrjs — Tue, 02 Dec 2014 12:32:04 GMT

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

Re: Iterno()

Anonymous — Tue, 02 Dec 2014 12:54:25 GMT

Hi,

Thank yo so much. Helped me a lot.

Thanks,

Nisha loganathan