09-17-2013 01:15 AM
I am wondering how can i split the below data into the expected format, i tried with different DO statements but not getting the expected results.
The input have more than 1000 records, and each Team leader name presents at the end of team listing as highlighted, column ID is unique.
Input data format:
|14||star warriors||20||Mike Team|
|34||Rock Stars||10||John Team|
|124||Team AA||23||Bob Team|
09-17-2013 02:17 AM
First, you need a rule how to identify teams, and looking at this data, it seems to look at if Name contains the word 'Team'.
I am curious about the sort order, how did this happen, is it done manually?
I would first add a current sort order id to the data (sortorder = _n_).
The resort the data in reverse so that Teams will in front of their members,
Then you could create your TeamLeader variable using RETAIN and conditional processing on Name.
09-17-2013 02:31 AM
Actually, this data is pull from third party system, the word "Team" i just used for understanding purpose.
the data does not have the word "Team", it should have JOHN, BOB, MIKE alone.
Also, the team list is not static. For EX : this month John can have 4 teams, but the next month the new team can be added or existing team can be deleted under him.
09-17-2013 05:19 AM
Have a look at the code below, it will identify the teams it also gives each team an id, with this data you should be able to build what you need. The rule to identify the team is, that the sum of all previous sales are equal to the team sales.
09-17-2013 05:31 AM
Neat, but what happens for some reason if there are only a team leader, no other...?
Or there are id with same sales within a team?
Still urge Angorwat to get some more consistent data.
09-18-2013 02:57 AM
Thanks Bruno and LinusH for your help.
It really give me an better idea to approach this issue, as the data is more messy than what I put in the example.
Getting closer to my answer now.