Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at myibi@ibi.com and provide your corporate email address, company, and name.


Connect to myibi
Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    [SOLVED] DQS: create extra rows based on value of a field

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] DQS: create extra rows based on value of a field
 Login/Join
 
Member
posted
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,


iWay Suite, Windows
 
Posts: 2 | Location: UK South | Registered: April 24, 2020Report This Post
Member
posted Hide Post
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.
 
Posts: 1 | Registered: June 23, 2020Report This Post
Member
posted Hide Post
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 Smiler


iWay Suite, Windows
 
Posts: 2 | Location: UK South | Registered: April 24, 2020Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    [SOLVED] DQS: create extra rows based on value of a field

Copyright © 1996-2020 Information Builders