Im looking for a solution to eliminate duplicate rows using...

[NYCBabak .]

You need to use the LAST syntax https://infocenter.informationbuilders.com/wf80/index.jsptopic=%2Fpubdocs%2Freporting%2FUsingFunctions%2Fsource%2Fdatasource4.htm

to find out if the value of ID||Category has changed. If it has, then concatenate the Status otherwise, leave it as is. So, create a DEFINE that contains ID||Category, then DEFINE another field that has STATUS_LIST. The syntax you need to use is

STATUS_LIST/A500=IF LAST IDCATEGORY EQ IDCATEGORY THEN STATUS_LIST||STATUS ELSE STATUS;

One requirement for LAST is your IDCATEGORY has to be in sort order. If youre in doubt thats the case in your data, you ought to create a report and HOLD the answer set and do the final work on the HOLD file. Below is an example you can follow using CAR file. It checks the value of LAST COUNTRY and if it doesnt change concatenates , and CAR to it. Otherwise, itll just use CAR.

TABLE FILE CAR

BY COUNTRY

BY CAR

ON TABLE HOLD

END

DEFINE FILE HOLD

CAR_CON/A400V=IF COUNTRY EQ LAST COUNTRY THEN (CAR_CON|| (, | CAR)) ELSE CAR;

END

TABLE FILE HOLD

SUM

CAR_CON AS Car Concatenated

BY COUNTRY AS Country

END

[Lenora Barnes]

Thank you for your time and assistance. I am using the LAST function but am not able to get it to work as Id like. Im also limited to using the GUI interface although your code does help. Ill keep trying! Thanks again.

[NYCBabak .]

image.png835577 60.6 KB

 

 

image.png1334774 46.9 KB

![image|690x438]

image.png1023479 49.9 KB

 

 

image.png845418 48.4 KB

[Lenora Barnes]

Im looking for a solution to eliminate duplicate rows using the infoassist gui. Each row is the same except for one field. I want to take these multiple rows and for the one field that is different, concatenate the values separated by a comma. I could also live with creating multiple fields within the row but it would have to dynamic as it can be one or many. This is my data:

ID Category Status Descr

101 Fruit A Apple

101 Fruit A Pear

101 Fruit I Kiwi

102 Fruit A Kiwi

102 Fruit A Grape

102 Fruit A Berry

103 Fruit I Apple

103 Fruit I Orange

104 Fruit A Orange

I would like my output to look like this:

ID Category Status Descr

101 Fruit A Apple, Pear

101 Fruit I Kiwi

102 Fruit A Kiwi, Grape, Berry

103 Fruit I Apple, Orange

104 Fruit A Orange

Id like the number of items in Descr to be dynamic.

Ive created a couple of Define fields but something is amiss. Hoping someone can help.

Define_concat:

ID || Category || Status

Define_my_list:

IF Define_concat EQ LAST Define_concat THEN ( SUBSTR ( 100 , (

LAST Define_my_list ) , 1 , 88 , 88 , A88 ) || ( ', | Descr )

) ELSE Descr

These are my Row Labels (BY)

ID

Category

Status

Define_concat

These are my Measures (SUM)

MAX.Define_my_list

I am limited to using InfoAssist GUI 8.2.06. Any help is much appreciated!

[Noel McAvoy]

Lenora, have you been able to make the LAST function work since your last post If not, perhaps you could post the code that is behind your InfoAssist report so we can help You can see the code by clicking on the icon showing a magnifying glass over a piece of paper (shown below).

[Lenora Barnes]

Thank you both for your help and advice! My apologies for not responding sooner. I have made this work. NYCBabak your screenshots helped tremendously.