Hi Guys,

I'm new to WebFOCUS, hope you can give me some advice.

I have a scenario to find out that how many children with the same father.

Is it possible to get below pattern serial number as below, it groups by father id.

S/N child_ID father_ID
1a child_A father_A
1b child_B father_A
1c child_C father_A
2a child_D father_B
2b child_E father_B
3a child_D father_C
3b child_F father_C

Thanks for your help ahead.

This message has been edited. Last edited by: junjie,

Hi Junjie,

You can use the below piece of code:
TABLE FILE X
PRINT
S/N
CHILD_ID
FATHER_ID
BY FATHER_ID NOPRINT
END

NOPRINT should help you with maintaining the order of columns. If you are looking for something else, please explain more.

Thanks,
Pravin

Hi Pravin,
Thanks for your fast reply.

Actually what I wanted is how to get below kind of serial number:
1a
1b
1c
2a
2b
3a
3b
3c

Hi and welcome to Focal Point.

What you need to do is order your records by the one that you wish to serialise - e.g. Father_ID -

-* The first portion of the code creates a temporary file containing your data
EX -LINES * EDAPUT MASTER,junjie,CV,FILE
FILENAME=junjie, SUFFIX=FOC
SEGNAME=SEG01, $
FIELDNAME=Father_ID, ,A9,A9,$
FIELDNAME=Child_ID, ,A9,A9,$
EDAPUT*
-RUN
CREATE FILE junjie
MODIFY FILE junjie
DATA
Father A,Child A,$
Father A,Child B,$
Father A,Child C,$
Father B,Child D,$
Father B,Child E,$
Father C,Child D,$
Father C,Child F,$
END
-RUN

TABLE FILE junjie
SUM COMPUTE CNTR/I3   = IF Father_ID EQ LAST Father_ID THEN LAST CNTR ELSE LAST CNTR + 1; NOPRINT
    COMPUTE SUFF_I/I3 = IF Father_ID NE LAST Father_ID THEN 97 ELSE LAST SUFF_I + 1; NOPRINT
    COMPUTE Serial_ID/A6 =LJUST(6,FPRINT(CNTR,'I3','A3')||HEXBYT(SUFF_I,'A3'),'A6');
 BY Father_ID
 BY Child_ID
END
-RUN

Good luck

T

Hi Tony,

Appreciate your effort.
Below codes work for me perfectly.

TABLE FILE junjie
SUM COMPUTE CNTR/I3 = IF Father_ID EQ LAST Father_ID THEN LAST CNTR ELSE LAST CNTR + 1; NOPRINT
COMPUTE SUFF_I/I3 = IF Father_ID NE LAST Father_ID THEN 97 ELSE LAST SUFF_I + 1; NOPRINT
COMPUTE Serial_ID/A6 =LJUST(6,FPRINT(CNTR,'I3','A3')||HEXBYT(SUFF_I,'A3'),'A6');
BY Father_ID
BY Child_ID
END
-RUN

But I encountered error when I tried to run below codes to create dummy data, The error message is: Cannot include resource specified IBFS:/-LINES
-* The first portion of the code creates a temporary file containing your data
EX -LINES * EDAPUT MASTER,junjie,CV,FILE
FILENAME=junjie, SUFFIX=FOC
SEGNAME=SEG01, $
FIELDNAME=Father_ID, ,A9,A9,$
FIELDNAME=Child_ID, ,A9,A9,$
EDAPUT*
-RUN

CREATE FILE junjie
MODIFY FILE junjie
DATA
Father A,Child A,$
Father A,Child B,$
Father A,Child C,$
Father B,Child D,$
Father B,Child E,$
Father C,Child D,$
Father C,Child F,$
END
-RUN

Could you help me on this as well so that I can create dummy data for test purpose in the near future.

Hi Junjie,

That error is because you are executing this from a content folder whereas I created the example within an application folder.

To resolve this in a content folder scenario, just prefix the line EX - LINES with -MRNOEDIT EX -LINES etc.

If you want to learn about creating temporary test data, search the forum for examples and you will find many. a possible search would be on EDAPUT.

Also keep the Tech Library document URL to hand - Link to 8.1 Docs

T

Hi Tony,

You are great, it's working fine after I added the prefix -MRNOEDIT you mentioned.

Thank you so much for pointing out the resource URL, it very useful not only for me, also for those who will see this post in the future.

This question can be remarked as SOLVED.

Hi Junjie,

You should mark this post as solved by editing your very first text where you will be able to change the subject.

Just add [SOLVED} to the beginning of the subject line.

T