Hi,

I have a field in a FOCUS table that contains product codes separated by commas and I would like to sort these codes from lowest to highest.
e.g.: when I have as input "2568, 5685, 1025" I would like to get this result: "1025, 2568, 5685".
Is there a function or a command in WebFOCUS that does this?

Regards,
Mathijs.

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

Mathijs,

I don't think there is any such functions readily available in the rpoduct.
I'm afraid you'll have to come up with some code to do this yourself.

You should be able to do something like this:

TABLE FILE
PRINT
COMPUTE CNTR/I5 = LAST CNTR + 1;
BY NOPRINT
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS HOLD1
END

DEFINE FILE HOLD1
CODELIST/A1000 = IF CNTR EQ 1 THEN ELSE
LAST CODELIST || ',';
END

TABLE FILE HOLD1
SUM CODELIST
BY HIGHEST 1 CNTR
END

You will then have to do something to trim off the trailing comma.

Not seeing how that last snippet is going to work. The data that needs to be sorted is all in the same record, so this can't really accomplish what's needed.

As GamP say, there is no functions to accomplish what you want. You're basically just wanting to reorganize the contents of an alpha string. The only way I see this happening is to use some implementation of the MacGuyver technique to create multiple records for each one record, where each individual record contains one of the values from your string. Then you can sort them low to high and hold them. Then re-read your hold file concatentating them back together using the LAST functionality. Not much time to play around with this right now, but that's the gist of what it would take.

Mathijs,

Answers to three questions may help in solving this:

Are the values always numeric?
Are there always the same number of values in each set of product codes?
Are the values always unique in each set of product codes?

This message has been edited. Last edited by: Francis Mariani,

Based on some assumptions, here is a possible solution, using a DEFINE FUNCTION (Creating Reports With Graphical Tools > Creating Temporary Fields > Creating Temporary Fields Unrelated to Master Files):

-* horiz1.fex - Use a DEFINE FUNCTION to resort numeric strings in one field
-* Francis Mariani - 2009/11/25

-SET &ECHO='ALL';

SET ASNAMES=ON
SET HOLDLIST=PRINTONLY
SET HOLDFORMAT=ALPHA
-RUN

-*== Create a file to test with ============================
FILEDEF PRODUCT DISK product.txt
-RUN

-WRITE PRODUCT Apples       140, 120, 4550, 1, 333
-WRITE PRODUCT Oranges      114, 1104, 400, 200, 1035
-WRITE PRODUCT Bananas      55420, 100, 42550, 99, 123
-WRITE PRODUCT Pomegranates 3400, 1445, 472, 235, 7321

FILEDEF MASTER DISK product.mas
-RUN

-WRITE MASTER FILENAME=PRODUCT, SUFFIX=FIX, $
-WRITE MASTER SEGNAME=PRODUCT, $
-WRITE MASTER FIELDNAME=PRODUCT_NAME, FORMAT=A12, ACTUAL=A12, $
-WRITE MASTER FIELDNAME=FILL1,   FORMAT=A1, ACTUAL=A1, $
-WRITE MASTER FIELDNAME=PRODUCT_CODES, FORMAT=A30, ACTUAL=A30, $
-RUN

-*== Define the function ===================================
DEFINE FUNCTION SORTSTR(INSTR/A30)
INSTR1/I9 = EDIT(GETTOK(INSTR, 30, 1, ',', 10, 'A9'));
INSTR2/I9 = EDIT(GETTOK(INSTR, 30, 2, ',', 10, 'A9'));
INSTR3/I9 = EDIT(GETTOK(INSTR, 30, 3, ',', 10, 'A9'));
INSTR4/I9 = EDIT(GETTOK(INSTR, 30, 4, ',', 10, 'A9'));
INSTR5/I9 = EDIT(GETTOK(INSTR, 30, 5, ',', 10, 'A9'));

OUTSTR1/P9 = MIN(INSTR1, INSTR2, INSTR3, INSTR4, INSTR5);

OUTSTR1L/I2 = IF OUTSTR1 EQ INSTR1 THEN 1 ELSE
              IF OUTSTR1 EQ INSTR2 THEN 2 ELSE
              IF OUTSTR1 EQ INSTR3 THEN 3 ELSE
              IF OUTSTR1 EQ INSTR4 THEN 4 ELSE 5;

OUTSTR2/P9 = IF OUTSTR1L EQ 1 THEN MIN(INSTR2, INSTR3, INSTR4, INSTR5) ELSE
             IF OUTSTR1L EQ 2 THEN MIN(INSTR1, INSTR3, INSTR4, INSTR5) ELSE
             IF OUTSTR1L EQ 3 THEN MIN(INSTR1, INSTR2, INSTR4, INSTR5) ELSE
             IF OUTSTR1L EQ 4 THEN MIN(INSTR1, INSTR2, INSTR3, INSTR5) ELSE
             IF OUTSTR1L EQ 5 THEN MIN(INSTR1, INSTR2, INSTR3, INSTR4);

OUTSTR2L/I2 = IF OUTSTR2 EQ INSTR1 THEN 1 ELSE
              IF OUTSTR2 EQ INSTR2 THEN 2 ELSE
              IF OUTSTR2 EQ INSTR3 THEN 3 ELSE
              IF OUTSTR2 EQ INSTR4 THEN 4 ELSE 5;

OUTSTR3/P9 = IF OUTSTR1L EQ 1 AND OUTSTR2L EQ 2 THEN MIN(INSTR3, INSTR4, INSTR5) ELSE
             IF OUTSTR1L EQ 1 AND OUTSTR2L EQ 3 THEN MIN(INSTR2, INSTR4, INSTR5) ELSE
             IF OUTSTR1L EQ 1 AND OUTSTR2L EQ 4 THEN MIN(INSTR2, INSTR3, INSTR5) ELSE
             IF OUTSTR1L EQ 1 AND OUTSTR2L EQ 5 THEN MIN(INSTR2, INSTR3, INSTR4) ELSE

             IF OUTSTR1L EQ 2 AND OUTSTR2L EQ 1 THEN MIN(INSTR3, INSTR4, INSTR5) ELSE
             IF OUTSTR1L EQ 2 AND OUTSTR2L EQ 3 THEN MIN(INSTR1, INSTR4, INSTR5) ELSE
             IF OUTSTR1L EQ 2 AND OUTSTR2L EQ 4 THEN MIN(INSTR1, INSTR3, INSTR5) ELSE
             IF OUTSTR1L EQ 2 AND OUTSTR2L EQ 5 THEN MIN(INSTR1, INSTR3, INSTR4) ELSE

             IF OUTSTR1L EQ 3 AND OUTSTR2L EQ 1 THEN MIN(INSTR2, INSTR4, INSTR5) ELSE
             IF OUTSTR1L EQ 3 AND OUTSTR2L EQ 2 THEN MIN(INSTR1, INSTR4, INSTR5) ELSE
             IF OUTSTR1L EQ 3 AND OUTSTR2L EQ 4 THEN MIN(INSTR1, INSTR2, INSTR5) ELSE
             IF OUTSTR1L EQ 3 AND OUTSTR2L EQ 5 THEN MIN(INSTR1, INSTR2, INSTR4) ELSE

             IF OUTSTR1L EQ 4 AND OUTSTR2L EQ 1 THEN MIN(INSTR2, INSTR3, INSTR5) ELSE
             IF OUTSTR1L EQ 4 AND OUTSTR2L EQ 2 THEN MIN(INSTR1, INSTR3, INSTR5) ELSE
             IF OUTSTR1L EQ 4 AND OUTSTR2L EQ 3 THEN MIN(INSTR1, INSTR2, INSTR5) ELSE
             IF OUTSTR1L EQ 4 AND OUTSTR2L EQ 5 THEN MIN(INSTR1, INSTR2, INSTR3) ELSE

             IF OUTSTR1L EQ 5 AND OUTSTR2L EQ 1 THEN MIN(INSTR2, INSTR3, INSTR4) ELSE
             IF OUTSTR1L EQ 5 AND OUTSTR2L EQ 2 THEN MIN(INSTR1, INSTR3, INSTR4) ELSE
             IF OUTSTR1L EQ 5 AND OUTSTR2L EQ 3 THEN MIN(INSTR1, INSTR2, INSTR4) ELSE
             IF OUTSTR1L EQ 5 AND OUTSTR2L EQ 4 THEN MIN(INSTR1, INSTR2, INSTR3);

OUTSTR3L/I2 = IF OUTSTR3 EQ INSTR1 THEN 1 ELSE
              IF OUTSTR3 EQ INSTR2 THEN 2 ELSE
              IF OUTSTR3 EQ INSTR3 THEN 3 ELSE
              IF OUTSTR3 EQ INSTR4 THEN 4 ELSE 5;

OUTSTR5/P9 = MAX(INSTR1, INSTR2, INSTR3, INSTR4, INSTR5);
OUTSTR5L/I2 = IF OUTSTR5 EQ INSTR1 THEN 1 ELSE
              IF OUTSTR5 EQ INSTR2 THEN 2 ELSE
              IF OUTSTR5 EQ INSTR3 THEN 3 ELSE
              IF OUTSTR5 EQ INSTR4 THEN 4 ELSE 5;

OUTSTR4/P9 = IF OUTSTR5L EQ 1 THEN MAX(INSTR2, INSTR3, INSTR4, INSTR5) ELSE
             IF OUTSTR5L EQ 2 THEN MAX(INSTR1, INSTR3, INSTR4, INSTR5) ELSE
             IF OUTSTR5L EQ 3 THEN MAX(INSTR1, INSTR2, INSTR4, INSTR5) ELSE
             IF OUTSTR5L EQ 4 THEN MAX(INSTR1, INSTR2, INSTR3, INSTR5) ELSE
             IF OUTSTR5L EQ 5 THEN MAX(INSTR1, INSTR2, INSTR3, INSTR4);

OUTSTR/A55 = PTOA(OUTSTR1, '(P9)', 'A9') || ', ' ||
             PTOA(OUTSTR2, '(P9)', 'A9') || ', ' ||
             PTOA(OUTSTR3, '(P9)', 'A9') || ', ' ||
             PTOA(OUTSTR4, '(P9)', 'A9') || ', ' ||
             PTOA(OUTSTR5, '(P9)', 'A9');

SORTSTR/A30 = SUBSTR(55, SQUEEZ(55, OUTSTR, 'A55'), 1, 30, 30, 'A30');
END
-RUN

TABLE FILE PRODUCT
PRINT
PRODUCT_CODES
COMPUTE PRODUCT_CODES1/A30 = SORTSTR(PRODUCT_CODES);
END
-RUN

I can explain the code if required.

Let's hope there are only 5 possible values of product code in the string and not 100.

I know! 5 is already ugly! 4 is alright.

Francis,

Thanks a lot this does exactly what I needed!
However as you did not have all the clues there is going to be some modification needed if you can.
Here are the answers to your first post, sorry I did not have much time to reply earlier:
- yes the values are always numeric but,
- the number of values in one record is actually variable and ranges from 1 to up to 6 values in one record
- each value is assumed to be unique in each set

I gave this a try with my data and it seems to work great apart from the fact that the number of values being variable the output is not completely right. I guess there will be some counting involved but I don’t know if this is possible without making the thing too complex. The field I am working on is named TXCOMBI and its actual size is A80. Hope you can work out something!

Please let me know if you need any more details.

Mathijs.

Wow Francis that is a nice solution!
Jodye

Except that extending this to six values makes it completely unusable. And there's a weird thing happening where for some rows, the values of the previous rows are overriding the current rows!

OK, this code almost works!

I've temporarily converted the DEFINE FUNCTION into a DEFINE FILE so we can see the individual fields.

I have a problem with row 5, one of the input field values(93) does not get copied to the output field... I have to check the code.

-* horiz1.fex - Use a DEFINE FUNCTION to resort numeric strings in one field
-* Francis Mariani - 2009/11/25

-SET &ECHO='ALL';

SET ASNAMES=ON
SET HOLDLIST=PRINTONLY
SET HOLDFORMAT=ALPHA
-RUN

-*== Create a file to test with ============================
FILEDEF PRODUCT DISK product.txt
-RUN

-WRITE PRODUCT Apples       140, 120, 4550, 1, 333, 27
-WRITE PRODUCT Oranges      114, 1104, 400
-WRITE PRODUCT Pomegranates 3400, 1445, 472, 235, 7321, 943
-WRITE PRODUCT Cherries     125, 46426
-WRITE PRODUCT Mangoes      516, 9182, 1291, 93
-WRITE PRODUCT Bananas      11111, 33333, 22222, 44444, 55555, 16
-WRITE PRODUCT Clementines  2288

FILEDEF MASTER DISK product.mas
-RUN

-WRITE MASTER FILENAME=PRODUCT, SUFFIX=FIX, $
-WRITE MASTER SEGNAME=PRODUCT, $
-WRITE MASTER FIELDNAME=PRODUCT_NAME, FORMAT=A12, ACTUAL=A12, $
-WRITE MASTER FIELDNAME=FILL1,   FORMAT=A1, ACTUAL=A1, $
-WRITE MASTER FIELDNAME=PRODUCT_CODES, FORMAT=A66, ACTUAL=A66, $
-RUN

DEFINE FILE PRODUCT
INSTR/A66 = PRODUCT_CODES;
-*== Define the function ===================================
-*DEFINE FUNCTION SORTSTR(INSTR/A66)
INSTR1/I9 = EDIT(GETTOK(INSTR, 66, 1, ',', 10, 'A9'));
INSTR2/I9 = EDIT(GETTOK(INSTR, 66, 2, ',', 10, 'A9'));
INSTR3/I9 = EDIT(GETTOK(INSTR, 66, 3, ',', 10, 'A9'));
INSTR4/I9 = EDIT(GETTOK(INSTR, 66, 4, ',', 10, 'A9'));
INSTR5/I9 = EDIT(GETTOK(INSTR, 66, 5, ',', 10, 'A9'));
INSTR6/I9 = EDIT(GETTOK(INSTR, 66, 6, ',', 10, 'A9'));

OUTSTR1/P9 = MIN(INSTR1, INSTR2, INSTR3, INSTR4, INSTR5, INSTR6);

OUTSTR1L/I2 = IF OUTSTR1 EQ INSTR1 THEN 1 ELSE
              IF OUTSTR1 EQ INSTR2 THEN 2 ELSE
              IF OUTSTR1 EQ INSTR3 THEN 3 ELSE
              IF OUTSTR1 EQ INSTR4 THEN 4 ELSE
              IF OUTSTR1 EQ INSTR5 THEN 5 ELSE 6;

OUTSTR2/P9 = IF OUTSTR1L EQ 1 THEN MIN(INSTR2, INSTR3, INSTR4, INSTR5, INSTR6) ELSE
             IF OUTSTR1L EQ 2 THEN MIN(INSTR1, INSTR3, INSTR4, INSTR5, INSTR6) ELSE
             IF OUTSTR1L EQ 3 THEN MIN(INSTR1, INSTR2, INSTR4, INSTR5, INSTR6) ELSE
             IF OUTSTR1L EQ 4 THEN MIN(INSTR1, INSTR2, INSTR3, INSTR5, INSTR6) ELSE
             IF OUTSTR1L EQ 5 THEN MIN(INSTR1, INSTR2, INSTR3, INSTR4, INSTR6) ELSE
             IF OUTSTR1L EQ 6 THEN MIN(INSTR1, INSTR2, INSTR3, INSTR4, INSTR5) ELSE 0;

OUTSTR2L/I2 = IF OUTSTR2 EQ INSTR1 THEN 1 ELSE
              IF OUTSTR2 EQ INSTR2 THEN 2 ELSE
              IF OUTSTR2 EQ INSTR3 THEN 3 ELSE
              IF OUTSTR2 EQ INSTR4 THEN 4 ELSE
              IF OUTSTR2 EQ INSTR5 THEN 5 ELSE 6;

OUTSTR3/P9 = IF OUTSTR1L EQ 1 AND OUTSTR2L EQ 2 THEN MIN(INSTR3, INSTR4, INSTR5, INSTR6) ELSE
             IF OUTSTR1L EQ 1 AND OUTSTR2L EQ 3 THEN MIN(INSTR2, INSTR4, INSTR5, INSTR6) ELSE
             IF OUTSTR1L EQ 1 AND OUTSTR2L EQ 4 THEN MIN(INSTR2, INSTR3, INSTR5, INSTR6) ELSE
             IF OUTSTR1L EQ 1 AND OUTSTR2L EQ 5 THEN MIN(INSTR2, INSTR3, INSTR4, INSTR6) ELSE
             IF OUTSTR1L EQ 1 AND OUTSTR2L EQ 6 THEN MIN(INSTR2, INSTR3, INSTR4, INSTR5) ELSE

             IF OUTSTR1L EQ 2 AND OUTSTR2L EQ 1 THEN MIN(INSTR3, INSTR4, INSTR5, INSTR6) ELSE
             IF OUTSTR1L EQ 2 AND OUTSTR2L EQ 3 THEN MIN(INSTR1, INSTR4, INSTR5, INSTR6) ELSE
             IF OUTSTR1L EQ 2 AND OUTSTR2L EQ 4 THEN MIN(INSTR1, INSTR3, INSTR5, INSTR6) ELSE
             IF OUTSTR1L EQ 2 AND OUTSTR2L EQ 5 THEN MIN(INSTR1, INSTR3, INSTR4, INSTR6) ELSE
             IF OUTSTR1L EQ 2 AND OUTSTR2L EQ 6 THEN MIN(INSTR1, INSTR3, INSTR4, INSTR5) ELSE

             IF OUTSTR1L EQ 3 AND OUTSTR2L EQ 1 THEN MIN(INSTR2, INSTR4, INSTR5, INSTR6) ELSE
             IF OUTSTR1L EQ 3 AND OUTSTR2L EQ 2 THEN MIN(INSTR1, INSTR4, INSTR5, INSTR6) ELSE
             IF OUTSTR1L EQ 3 AND OUTSTR2L EQ 4 THEN MIN(INSTR1, INSTR2, INSTR5, INSTR6) ELSE
             IF OUTSTR1L EQ 3 AND OUTSTR2L EQ 5 THEN MIN(INSTR1, INSTR2, INSTR4, INSTR6) ELSE
             IF OUTSTR1L EQ 3 AND OUTSTR2L EQ 6 THEN MIN(INSTR1, INSTR2, INSTR4, INSTR5) ELSE

             IF OUTSTR1L EQ 4 AND OUTSTR2L EQ 1 THEN MIN(INSTR2, INSTR3, INSTR5, INSTR6) ELSE
             IF OUTSTR1L EQ 4 AND OUTSTR2L EQ 2 THEN MIN(INSTR1, INSTR3, INSTR5, INSTR6) ELSE
             IF OUTSTR1L EQ 4 AND OUTSTR2L EQ 3 THEN MIN(INSTR1, INSTR2, INSTR5, INSTR6) ELSE
             IF OUTSTR1L EQ 4 AND OUTSTR2L EQ 5 THEN MIN(INSTR1, INSTR2, INSTR3, INSTR6) ELSE
             IF OUTSTR1L EQ 4 AND OUTSTR2L EQ 6 THEN MIN(INSTR1, INSTR2, INSTR3, INSTR5) ELSE

             IF OUTSTR1L EQ 5 AND OUTSTR2L EQ 1 THEN MIN(INSTR2, INSTR3, INSTR4, INSTR6) ELSE
             IF OUTSTR1L EQ 5 AND OUTSTR2L EQ 2 THEN MIN(INSTR1, INSTR3, INSTR4, INSTR6) ELSE
             IF OUTSTR1L EQ 5 AND OUTSTR2L EQ 3 THEN MIN(INSTR1, INSTR2, INSTR4, INSTR6) ELSE
             IF OUTSTR1L EQ 5 AND OUTSTR2L EQ 4 THEN MIN(INSTR1, INSTR2, INSTR3, INSTR6) ELSE
             IF OUTSTR1L EQ 5 AND OUTSTR2L EQ 6 THEN MIN(INSTR1, INSTR2, INSTR3, INSTR4) ELSE

             IF OUTSTR1L EQ 6 AND OUTSTR2L EQ 1 THEN MIN(INSTR2, INSTR3, INSTR4, INSTR5) ELSE
             IF OUTSTR1L EQ 6 AND OUTSTR2L EQ 2 THEN MIN(INSTR1, INSTR3, INSTR4, INSTR5) ELSE
             IF OUTSTR1L EQ 6 AND OUTSTR2L EQ 3 THEN MIN(INSTR1, INSTR2, INSTR4, INSTR5) ELSE
             IF OUTSTR1L EQ 6 AND OUTSTR2L EQ 4 THEN MIN(INSTR1, INSTR2, INSTR3, INSTR5) ELSE
             IF OUTSTR1L EQ 6 AND OUTSTR2L EQ 5 THEN MIN(INSTR1, INSTR2, INSTR3, INSTR4) ELSE 0;

OUTSTR3L/I2 = IF OUTSTR3 EQ INSTR1 THEN 1 ELSE
              IF OUTSTR3 EQ INSTR2 THEN 2 ELSE
              IF OUTSTR3 EQ INSTR3 THEN 3 ELSE
              IF OUTSTR3 EQ INSTR4 THEN 4 ELSE
              IF OUTSTR3 EQ INSTR5 THEN 5 ELSE 6;

OUTSTR6/P9 = MAX(INSTR1, INSTR2, INSTR3, INSTR4, INSTR5, INSTR6);

OUTSTR6L/I2 = IF OUTSTR6 EQ INSTR1 THEN 1 ELSE
              IF OUTSTR6 EQ INSTR2 THEN 2 ELSE
              IF OUTSTR6 EQ INSTR3 THEN 3 ELSE
              IF OUTSTR6 EQ INSTR4 THEN 4 ELSE
              IF OUTSTR6 EQ INSTR5 THEN 5 ELSE 6;

OUTSTR5/P9 = IF OUTSTR6L EQ 1 THEN MAX(INSTR2, INSTR3, INSTR4, INSTR5, INSTR6) ELSE
             IF OUTSTR6L EQ 2 THEN MAX(INSTR1, INSTR3, INSTR4, INSTR5, INSTR6) ELSE
             IF OUTSTR6L EQ 3 THEN MAX(INSTR1, INSTR2, INSTR4, INSTR5, INSTR6) ELSE
             IF OUTSTR6L EQ 4 THEN MAX(INSTR1, INSTR2, INSTR3, INSTR5, INSTR6) ELSE
             IF OUTSTR6L EQ 5 THEN MAX(INSTR1, INSTR2, INSTR3, INSTR4, INSTR6) ELSE
             IF OUTSTR6L EQ 6 THEN MAX(INSTR1, INSTR2, INSTR3, INSTR4, INSTR5) ELSE 0;

OUTSTR5L/I2 = IF OUTSTR5 EQ INSTR1 THEN 1 ELSE
              IF OUTSTR5 EQ INSTR2 THEN 2 ELSE
              IF OUTSTR5 EQ INSTR3 THEN 3 ELSE
              IF OUTSTR5 EQ INSTR4 THEN 4 ELSE
              IF OUTSTR5 EQ INSTR5 THEN 5 ELSE 6;

OUTSTR4/P9 = IF OUTSTR6L EQ 1 AND OUTSTR5L EQ 2 THEN MAX(INSTR3, INSTR4, INSTR5, INSTR6) ELSE
             IF OUTSTR6L EQ 1 AND OUTSTR5L EQ 3 THEN MAX(INSTR2, INSTR4, INSTR5, INSTR6) ELSE
             IF OUTSTR6L EQ 1 AND OUTSTR5L EQ 4 THEN MAX(INSTR2, INSTR3, INSTR5, INSTR6) ELSE
             IF OUTSTR6L EQ 1 AND OUTSTR5L EQ 5 THEN MAX(INSTR2, INSTR3, INSTR4, INSTR6) ELSE
             IF OUTSTR6L EQ 1 AND OUTSTR5L EQ 6 THEN MAX(INSTR2, INSTR3, INSTR4, INSTR5) ELSE

             IF OUTSTR6L EQ 2 AND OUTSTR5L EQ 1 THEN MAX(INSTR3, INSTR4, INSTR5, INSTR6) ELSE
             IF OUTSTR6L EQ 2 AND OUTSTR5L EQ 3 THEN MAX(INSTR1, INSTR4, INSTR5, INSTR6) ELSE
             IF OUTSTR6L EQ 2 AND OUTSTR5L EQ 4 THEN MAX(INSTR1, INSTR3, INSTR5, INSTR6) ELSE
             IF OUTSTR6L EQ 2 AND OUTSTR5L EQ 5 THEN MAX(INSTR1, INSTR3, INSTR4, INSTR6) ELSE
             IF OUTSTR6L EQ 2 AND OUTSTR5L EQ 6 THEN MAX(INSTR1, INSTR3, INSTR4, INSTR5) ELSE

             IF OUTSTR6L EQ 3 AND OUTSTR5L EQ 1 THEN MAX(INSTR2, INSTR4, INSTR5, INSTR6) ELSE
             IF OUTSTR6L EQ 3 AND OUTSTR5L EQ 2 THEN MAX(INSTR1, INSTR4, INSTR5, INSTR6) ELSE
             IF OUTSTR6L EQ 3 AND OUTSTR5L EQ 4 THEN MAX(INSTR1, INSTR2, INSTR5, INSTR6) ELSE
             IF OUTSTR6L EQ 3 AND OUTSTR5L EQ 5 THEN MAX(INSTR1, INSTR2, INSTR4, INSTR6) ELSE
             IF OUTSTR6L EQ 3 AND OUTSTR5L EQ 6 THEN MAX(INSTR1, INSTR2, INSTR4, INSTR5) ELSE

             IF OUTSTR6L EQ 4 AND OUTSTR5L EQ 1 THEN MAX(INSTR2, INSTR3, INSTR5, INSTR6) ELSE
             IF OUTSTR6L EQ 4 AND OUTSTR5L EQ 2 THEN MAX(INSTR1, INSTR3, INSTR5, INSTR6) ELSE
             IF OUTSTR6L EQ 4 AND OUTSTR5L EQ 3 THEN MAX(INSTR1, INSTR2, INSTR5, INSTR6) ELSE
             IF OUTSTR6L EQ 4 AND OUTSTR5L EQ 5 THEN MAX(INSTR1, INSTR2, INSTR3, INSTR6) ELSE
             IF OUTSTR6L EQ 4 AND OUTSTR5L EQ 6 THEN MAX(INSTR1, INSTR2, INSTR3, INSTR5) ELSE

             IF OUTSTR6L EQ 5 AND OUTSTR5L EQ 1 THEN MAX(INSTR2, INSTR3, INSTR4, INSTR6) ELSE
             IF OUTSTR6L EQ 5 AND OUTSTR5L EQ 2 THEN MAX(INSTR1, INSTR3, INSTR4, INSTR6) ELSE
             IF OUTSTR6L EQ 5 AND OUTSTR5L EQ 3 THEN MAX(INSTR1, INSTR2, INSTR4, INSTR6) ELSE
             IF OUTSTR6L EQ 5 AND OUTSTR5L EQ 4 THEN MAX(INSTR1, INSTR2, INSTR3, INSTR6) ELSE
             IF OUTSTR6L EQ 5 AND OUTSTR5L EQ 6 THEN MAX(INSTR1, INSTR2, INSTR3, INSTR4) ELSE

             IF OUTSTR6L EQ 6 AND OUTSTR5L EQ 1 THEN MAX(INSTR2, INSTR3, INSTR4, INSTR5) ELSE
             IF OUTSTR6L EQ 6 AND OUTSTR5L EQ 2 THEN MAX(INSTR1, INSTR3, INSTR4, INSTR5) ELSE
             IF OUTSTR6L EQ 6 AND OUTSTR5L EQ 3 THEN MAX(INSTR1, INSTR2, INSTR4, INSTR5) ELSE
             IF OUTSTR6L EQ 6 AND OUTSTR5L EQ 4 THEN MAX(INSTR1, INSTR2, INSTR3, INSTR5) ELSE
             IF OUTSTR6L EQ 6 AND OUTSTR5L EQ 5 THEN MAX(INSTR1, INSTR2, INSTR3, INSTR4) ELSE 0;

OUTSTR4L/I2 = IF OUTSTR4 EQ INSTR1 THEN 1 ELSE
              IF OUTSTR4 EQ INSTR2 THEN 2 ELSE
              IF OUTSTR4 EQ INSTR3 THEN 3 ELSE
              IF OUTSTR4 EQ INSTR4 THEN 4 ELSE
              IF OUTSTR4 EQ INSTR5 THEN 5 ELSE 6;

OUTSTR1A/A11 = IF OUTSTR1 EQ 0 THEN '' ELSE PTOA(OUTSTR1, '(P9)', 'A9') || ', ';
OUTSTR2A/A11 = IF OUTSTR2 EQ 0 THEN '' ELSE PTOA(OUTSTR2, '(P9)', 'A9') || ', ';
OUTSTR3A/A11 = IF OUTSTR3 EQ 0 THEN '' ELSE PTOA(OUTSTR3, '(P9)', 'A9') || ', ';
OUTSTR4A/A11 = IF OUTSTR4 EQ 0 THEN '' ELSE PTOA(OUTSTR4, '(P9)', 'A9') || ', ';
OUTSTR5A/A11 = IF OUTSTR5 EQ 0 THEN '' ELSE PTOA(OUTSTR5, '(P9)', 'A9') || ', ';
OUTSTR6A/A11 = IF OUTSTR6 EQ 0 THEN '' ELSE PTOA(OUTSTR6, '(P9)', 'A9');

OUTSTR/A66 = OUTSTR1A | OUTSTR2A | OUTSTR3A | OUTSTR4A | OUTSTR5A | OUTSTR6A;

SORTSTR/A66 = SQUEEZ(66, OUTSTR, 'A66');
END
-RUN

TABLE FILE PRODUCT
PRINT
PRODUCT_CODES

INSTR1
INSTR2
INSTR3
INSTR4
INSTR5
INSTR6

OUTSTR1
OUTSTR2
OUTSTR3
OUTSTR4
OUTSTR5
OUTSTR6

SORTSTR

-*COMPUTE PRODUCT_CODES1/A66 = SORTSTR(PRODUCT_CODES);

ON TABLE SET PAGE NOLEAD
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
TYPE=REPORT, SQUEEZE=ON,
FONT='ARIAL', SIZE=8,
BORDER=1, BORDER-COLOR=SILVER,
$
END
-RUN

You're trying too hard -

Think outside the box and use the best tool available for the job -

-SET &String = ' 2568, 5685, 1025';
-SET &New_String = '';
FILEDEF ST_STRING DISK ST_STRING.TXT
-RUN
-SET &Out_String = STRREP(&String.LENGTH, &String.QUOTEDSTRING, 1, ',', 2, HEXBYT(13,'A1')||HEXBYT(10,'A1'), 100, 'A100');
-WRITE ST_STRING &Out_String

FILEDEF ST_STRING DISK CLEAR
-RUN

-SET &DOS_RC = SYSTEM(34 ,'SORT ST_STRING.TXT > EN_STRING.TXT', 'D4');
-IF &DOS_RC NE 0 THEN :Error;

FILEDEF EN_STRING DISK EN_STRING.TXT
-RUN

-READ EN_STRING, &Group
-REPEAT :Loop WHILE &IORETURN EQ 0;
-SET &New_String = &New_String || &Group || ',';
-READ EN_STRING, &Group
-:Loop

-TYPE &String sorted becomes &New_String
-EXIT

-:Error
-TYPE An error occured in the DOS Sort process

Edited: Didn't read the whole question - as in data fields - not variables

Ah well, another process to play with anyway

T

This message has been edited. Last edited by: Tony A,

I am trying too hard!

Ok, I'll post my try here:

SET ASNAMES=ON
SET HOLDLIST=PRINTONLY
SET HOLDFORMAT=ALPHA
-RUN

-*== Create a file to test with ============================
FILEDEF MASTER DISK product.mas
-RUN

-WRITE MASTER FILENAME=PRODUCT, SUFFIX=FOC, $
-WRITE MASTER SEGNAME=PRODUCT, SEGTYPE=S1, $
-WRITE MASTER FIELDNAME=PRODUCT_NAME , FORMAT=A14, $
-WRITE MASTER FIELDNAME=PRODUCT_CODES, FORMAT=A80, $
-RUN

-*== Create the file and enter some data ===================
CREATE FILE PRODUCT

MODIFY FILE PRODUCT
FIXFORM PRODUCT_NAME/13 PRODUCT_CODES/50
MATCH PRODUCT_NAME
ON MATCH GOTO TOP
ON NOMATCH INCLUDE
DATA
Apples       140, 120, 4550, 1, 333, 27
Oranges      114, 1104, 400
Pomegranates 3400, 1445, 472, 235, 7321, 943
Cherries     125, 46426
Mangoes      516, 9182, 1291, 93
Bananas      11111, 33333, 22222, 44444, 55555, 16, 1786, 432
Clementines  2288
END

-*== And now for the real thing ============================
FILEDEF OUT DISK out.ftm (LRECL 50 RECFM V

MODIFY FILE PRODUCT
COMPUTE PRODCODE/A10 = ;
COMPUTE CNTR/I4=;
NEXT PRODUCT_NAME
ON NEXT COMPUTE CNTR=1;
ON NEXT PERFORM PUT_OUT
ON NONEXT GOTO EXIT

CASE PUT_OUT
COMPUTE PRODCODE = LJUST(10,GETTOK(D.PRODUCT_CODE, 80, CNTR, ',', 10,'A10'), PRODCODE);
IF PRODCODE EQ ' ' THEN GOTO ENDCASE;
TYPE ON OUT "<D.PRODUCT_NAME><PRODCODE>"
COMPUTE CNTR = CNTR+1;
GOTO PUT_OUT
ENDCASE
DATA
END

FILEDEF MASTER DISK out.mas
-RUN

-WRITE MASTER FILENAME=OUT, SUFFIX=FIX, $
-WRITE MASTER SEGNAME=OUT, $
-WRITE MASTER FIELDNAME=DUMMY        , FORMAT=A2 , ACTUAL=A2 , $
-WRITE MASTER FIELDNAME=PRODUCT_NAME , FORMAT=A14, ACTUAL=A14, $
-WRITE MASTER FIELDNAME=PROD_CODE    , FORMAT=A10, ACTUAL=A10, $
-RUN
TABLE FILE OUT
SUM COMPUTE CODESINORDER/A80V = IF PRODUCT_NAME NE LAST PRODUCT_NAME THEN PROD_CODE ELSE LAST CODESINORDER || ',' || PROD_CODE;
BY  PRODUCT_NAME BY PROD_CODE
ON TABLE HOLD
END

TABLE FILE HOLD
SUM MAX.CODESINORDER
BY PRODUCT_NAME
END

It'll work for any number of products in your field. It may need to be adjusted for other fields to be shown in the report, you could do this by means of a join from the last hold file to your original focus file.

Hope this helps ...

I have tried to adapt Tony and GamP's procedures to my master file but being a newbie on WebFocus I'm having some trouble finding out what I must change to fit my master file.

Here is my master file:

FILENAME=PANEL , SUFFIX=FOC , $
SEGMENT=SEG01, SEGTYPE=S1, $
FIELDNAME=PRODUCT, ALIAS=E01, USAGE=A10, $
FIELDNAME=TXCOMBI, ALIAS=E02, USAGE=A80, $

It is originally created by loading a csv file into a FOC file using FILEDEF and the output will also be a FOC file.
The field TXCOMBI is the field that contains the codes to be sorted. There are more fields in this master that must be kept as such but I have only kept field PRODUCT for the example.

Thanks in advance and sorry for the late feedback (pretty busy at the moment )

Mathijs,

Just to show that there is more than 1 way to tackle a problem with WebFocus, each having its measure of ingenuity...

  
-* File Mathijs1.fex
FILEDEF PRODUCT DISK product.txt
-RUN
-* Creating the data (added an X in order to have an 80 char field)
-WRITE PRODUCT Apples         140, 120, 4550, 1, 333, 27                                                      X
-WRITE PRODUCT Oranges        114, 1104, 400                                                                  X
-WRITE PRODUCT Pomegranates   3400, 1445, 472, 235, 7321, 943                                                 X
-WRITE PRODUCT Cherries       125, 46426                                                                      X
-WRITE PRODUCT Mangoes        516, 9182, 1291, 93                                                             X
-WRITE PRODUCT Bananas        11111, 33333, 22222, 44444, 55555, 16                                           X
-WRITE PRODUCT Clementines    2288                                                                            X
-RUN
-* Creating the MASTER for the data
EX -LINES 6 EDAPUT MASTER,PRODUCT,C,MEM
 FILENAME=PRODUCT    , SUFFIX=FIX
 SEGMENT=PRODUCT, SEGTYPE=S0
 FIELDNAME=PRODUCT,  ALIAS=PRODUCT,  USAGE=A15, ACTUAL=A15, $
 FIELDNAME=NLIST,    ALIAS=NLIST,    USAGE=A80, ACTUAL=A80, $
 FIELDNAME=NCHAR,    ALIAS=NCHAR,    USAGE=A1,  ACTUAL=A1,  $
-RUN
-* Retrieving the values from each field (up to 6) 
DEFINE FILE PRODUCT
-REPEAT #GETVAL FOR &I FROM 1 TO 6;
VAL&I / I9 =EDIT(GETTOK(NLIST, 80, &I, ',', 9, 'A9'));
-#GETVAL
END
-* Output the values separately
TABLE FILE PRODUCT
PRINT 
-REPEAT #PVAL FOR &I FROM 1 TO 6;
VAL&I 
-#PVAL
BY PRODUCT
ON TABLE SAVE AS VLIST
END
-* Create an "alternate" master for this file in order to access each value as a different record
EX -LINES 6 EDAPUT MASTER,VLIST,C,MEM
 FILENAME=VLIST    , SUFFIX=FIX
 SEGMENT=VLIST, SEGTYPE=S0
 FIELDNAME=PRODUCT,  ALIAS=PRODUCT,  USAGE=A15, ACTUAL=A15, $
 SEGMENT=VALS, PARENT=VLIST, OCCURS=VARIABLE 
 FIELDNAME=VAL,      ALIAS=VAL,      USAGE=I9,  ACTUAL=A9, $
-RUN
-* Sort the values
TABLE FILE VLIST
BY PRODUCT
BY VAL
IF VAL NE 0
ON TABLE HOLD
END
-* Concatenate the sorted values
DEFINE FILE HOLD
AVAL/A9=RJUST(9, FTOA(VAL, '(D9c)', 'A9'), 'A9');
VAL_LIST/A80=IF PRODUCT NE LAST PRODUCT THEN AVAL ELSE
                SUBSTR(80, LAST VAL_LIST, 1, 70, 70, 'A70') || ',' || AVAL;
END
-* Output the sorted field
TABLE FILE HOLD
SUM MAX.VAL_LIST
BY PRODUCT
END

I would have done it your way, Danny.

From you, Ginny, it is a big compliment.
Thanks

Danny, it looks like a great solution. My only concern is that you are hardwiring the EDAPUT for that file within the FOCEXEC. I am an adamant proponent of 'write ahead code'. Personally I would prefer to create the holdmast on the fly and based upon what is being 'fed in'. Hey just my thoughts.

But then you can't use the OCCURS clause which is the key to the whole solution. And in Danny's example, the occurs is variable which takes care of an unknown numbers of values.

Ginny I would refer you to my article on the IBI site for flexible reporting. Admittedly my particular report was a bit more complex.
http://www.informationbuilders.../FlexibleReport.html

I shall post all the code for the article on my website towards the weekend,for anyone who is interested in the technique. http://www.infogoldusa.com

The code HAS BEEN POSTED.
regards Steve

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

Hi all
As promised I have posted the "Creating dynamic reports at run time" code on my website http://www.infogoldusa.com
Should you have any questions or concerns, please feel free to contact me.

I like your solution too Danny, works fine and easy to adapt to my procedure !

Thanks !

quote:

Just to show that there is more than 1 way to tackle a problem with WebFocus, each having its measure of ingenuity...

Ditto for this late entry.

-* Split the TXCOMBI codes into a single column of values in multiple hold files.
-REPEAT :ENDREPEAT1 FOR &I FROM 1 TO 10
-SET &HOLDNAME = 'HOLD' || '&I';
DEFINE FILE PANEL
 VAL_A/A13 = GETTOK(TXCOMBI,80,&I,',',13,'A13');
 VAL_N/D13 = ATODBL(VAL_A,'13','D13');
END
TABLE FILE PANEL
 PRINT PRODUCT VAL_A VAL_N
 WHERE VAL_N NE 0 ;
 ON TABLE HOLD AS &HOLDNAME.EVAL
END
-RUN
-IF &LINES EQ 0 GOTO :EXITREPEAT1 ;
-:ENDREPEAT1
-:EXITREPEAT1

-* Combine/merge the hold files and sort the codes by product.
-SET &NUM_REPEATS = &I - 2 ;
TABLE FILE HOLD1
 PRINT VAL_A
 BY PRODUCT
 BY VAL_N
 ON TABLE HOLD AS VALHOLD
-REPEAT :ENDREPEAT2 FOR &I FROM 2 TO &NUM_REPEATS
-SET &HOLDNAME = 'HOLD' || '&I';
 MORE
 FILE &HOLDNAME.EVAL
-:ENDREPEAT2
END
-RUN

-* Put the codes back into strings in one column.
DEFINE FILE VALHOLD
 VALSTRING/A80 = IF PRODUCT NE LAST PRODUCT THEN VAL_A ELSE TRIM('B',LAST VALSTRING,80,' ',1,'A65') || ','  || VAL_A ;
END
TABLE FILE VALHOLD
 SUM LST.VALSTRING AS 'TXCOMBI'
 BY PRODUCT
END

This message has been edited. Last edited by: Dan Satchell,

Nice twist Dan, but one thing I would point out is that you are processing the entire data 10 times where as Danny only parses the data twice. Not too bad if you only have hundreds of records, but excessive if they number in the millions!

Hmm, I know which one I'd use

T

quote:

... one thing I would point out is that you are processing the entire data 10 times where as Danny only parses the data twice. Not too bad if you only have hundreds of records, but excessive if they number in the millions!

Tony: An excellent observation - although if Mathijs is correct that a maximum of 6 codes can occur for any product, then my code would make a maximum of seven passes through the data in the parsing step. Mathijs did indicate earlier that the original data comes from a CSV file, so I assumed it wasn't millions of records - but Danny's code would definitely prove more efficient. I was interested in avoiding the need for an alternative master.

This message has been edited. Last edited by: Dan Satchell,