Routine to count unique data records.

[Former Member]

Hello,

I am doing flat file data uploading into the InfoCube. One InfoObject is that of the TICKET_ID. I have another InfoObject

which counts the number of tickets ie. for each unique Ticket ID the count of ticket should be equal to one.

However in the flat file the TICKET_ID repeats multiple number of times. I want to write a routine in the transformation which maps the TICKET_ID to the number of tickets. For each of the unique ticket ID the Number of Ticket should be equal to one. For the TICKET_ID's repeating the number of ticket should be equal to one.

Can you please help me out with the routine.

Thanks in advance.

[Former Member]

hi

Have u got the solution or not??

Still u can try in front end itself....May be u try this way...

Create a selection & add ur char in tht & then create a KF with tht selection & found a counter...

Chk it out..

Cheers

BI Learner

[Former Member]

Problem Solved.

Pls refer to the following link:

https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/009819ab-c96e-2910-bbb2-c85f7bde...

[Former Member]

No need for routine

Create a KF for count of ticket

In the Business Explorer tab for creation of infoobject count of ticket give Aggregation as SUM, exception aggregation as Counter of all values and give aggregate reference charecteristics as Ticket ID

Upload the data in cube, execute the report. Results will be matching the count on flat file data

[Former Member]

Hey SDBI

Thanks for this very helpful answer.

However my ticket ID is not a number. It contains ID's like HD00000698 and hence i have taken it as a character.

[former_member189638]

Hi Siddhi,

There should not be any problem if your tickect id is a character. Still you can proceed with teh steps mentioned by SDBI.

Did u try that out?? Is it giving you some error???

[Former Member]

Yes I did try it out. It gives me an error saying that Ticket ID could not be recognised as number.

[Former Member]

Update the data first in ODS overwrite mode and then pass the data to the cube. Ticket ID should be Key field in the ODS .But maintain the settings mentioned above. That will solve your problem

[Former Member]

Hello,

I cant take the data into an ODS this is because my flat file looks like this:

TicketID Hours per Ticket

HD0001 2.30

HD0001 6.00

HD0002 21

HD0002 15

HD0002 9

HD0003 0.30

HD0004 0.45.......................

If i create an ods with TicketID as key field there will be an error in calculating the total hours per ticket.

What i want to display in the cube is as follows:

TicketID Hours per Ticket No of Tickets

HD0001 2.30 1

HD0001 6.00 0

HD0002 21 1

HD0002 15 0

HD0002 9 0

HD0003 0.30 1

HD0004 0.45 1

Thanks.