Jump to content
The ibi Community has moved to a new platform: Please Sign In and choose Forgot Password to continue

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


Lenora Barnes

Recommended Posts

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

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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).
Link to comment
Share on other sites

  • 3 weeks later...

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
  • Create New...