I hope i'm posting this in the right place.

I have a requirement to get rid of a quantity field in a dataset when present. I want to take the value and then add a number of rows based on that value and assign a new key ID.

So my example is

Key, Name , Quantity
1, Hat, 3
2,Gloves,1

Output

OldKey, Name, New_Key
1 ,Hat, 1
1,Hat, 2
1,Hat,3
2,Gloves,4

is this possible in DQS? i know i can make a new key with sequence generator but how do i make duplicate rows based on value of the quantity field?

This message has been edited. Last edited by: Karoshi,

That is an interesting challenge.

You can do it the following way in DQS (there are probably more ways to do it):

1) Create a new Column

Replicate

using the function

replicate("A ", Quantity)

This will basically create a new attribute with the value "A " (including a space) times the number of quantities you need (e.g. "A A " for Quantity 2, "A A A " for quantity 3, etc).

2) Now you can use the Splitter Operator to split the record. Use "All Sentence Column" the newly created column "Replicate", Add a new column "OneWord" (this is actually not needed) and we need the "Record Description Column", use e.g. "RecDesc".

This newly "RecDesc" Column basically indentifies your splitted records using an index like this (0:3:1). We are interested in the last part of the Record Descriptor, which indentifies the index of the current group in which we are.

3) So finally remove everything from the RecordDescriptor until the last ':'

toInteger(substituteAll('^.*:', '', RecDesc))

That should be it I believe.

Thank you, that's a great solution and works perfectly. I was looking at the splitter wondering how i could get it to do what i wanted and never thought of building a custom string to split in a step before. thank you very much