Dear Support, is there any example to generate sequential values and assigning it to row as row id after sorting the data, for example:
| Item | ItemColor | Location | Order |
| Prod 1 | Blue | Web | 1 |
| Prod 2 | Red | Web | 1 |
| Prod 2 | Blue | Web | 2 |
| Prod 3 | Green | Web | 1 |
| Prod 3 | Blue | Web | 2 |
| Prod 3 | Red | Web | 3 |
| Prod 3 | Blue | Web | 4 |
In the above, Prod 1 has only one color "Blue" so order is 1
Prod 2 has 2 values so the order is 1 and 2
Prod 3 has 4 colors so the order is 1,2,3 & 4.
Kind regards,
-
Hi CloverDxNewBie,
From the information provided, I understand you want to add a new Field to every row called “rowID” with increasing value.
In this case, the easiest way is very basic coding in MAP component.
To achieve the row ID field, please, follow the instructions:
- Duplicate the Existing metadata from the source file and add a new integer field called rowID.
- Add the MAP component to the graph and put the new created metadata on the Output Edge of the MAP.
- Open the Transform property of the MAP component, go to the Source card and add this code to the function:
integer rowID = 1; // Create Integer variable and set it to 1.
function integer transform() {
$out.0.* = $in.0.*; // Use Wildcard to assign identical fields.
$out.0.rowID = rowID; // Assign the variable to the rowID field.
rowID ++; // increases the rowID by one every record.
return ALL;
}
- Now if you run the Graph, you should see on the output edge of the MAP component a field rowID with incrementing numbers.
If you want to achieve more complex rowIDs or use more functionalities associated with sequences, you can use the Sequence property in CloverDX Designer in the Outline section. More about Sequences here.I hope this will help, but if you want to change any existing fields based on any rules and don't want to create a new field, could you please be more specific about your goals?
Best regards, Tom.
- Duplicate the Existing metadata from the source file and add a new integer field called rowID.
-
Hi,
From your description of the use-case, it seems to me, that you actually want to populate the field Order. Basically numbering the members of groups - defined by Item values, perhaps ?
In that case, the code would need to be modified slightly:
integer rowID = 1; // Create Integer variable and set it to 1.
string ItemName = "";
function integer transform() {
//check whether we see new group in the input data
if (ItemName != $in.0.Item){
rowId = 1; // reset the counter
ItemName = $in.0.Item; // store the new item group name
}
$out.0.* = $in.0.*; // Use Wildcard to assign identical fields.
$out.0.rowID = rowID; // Assign the variable to the rowID field.
rowID ++; // increases the rowID by one every record.
return ALL;
}Note: This code will work only if the data are sorted based on Item field.
-
Hi Tom - Thank you very much for your input, i did manage to make it work but I think i was not able to explain the problem more clearly, apologies but it gives me an idea.
Hi David - Yes, you got it right, thank you so much, it did work and really appreciated for your time and effort.
Please sign in to leave a comment.
Comments 3