Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
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
Go
New
Search
Notify
Tools
Reply
  
[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, 2020Reply With QuoteReport 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, 2020Reply With QuoteReport 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, 2020Reply With QuoteReport This Post
  Powered by Social Strata  
 

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-2018 Information Builders, leaders in enterprise business intelligence.