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.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


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
 
Karoshi
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
Christian Brabandt
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
Karoshi
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