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.

New TIBCO Community Coming Soon
In early summer, TIBCO plans to launch a new community—with a new user experience, enhanced search, and expanded capabilities for member engagement with answers and discussions! In advance of that, the current myibi community will be retired on April 30. We will continue to provide updates here on both the retirement of myibi and the new community launch.

What You Need to Know about Our New Community
We value the wealth of knowledge and engagement shared by community members and hope the new community will continue cultivating networking, knowledge sharing, and discussion.

During the transition period, from April 20th until the new community is launched this summer, myibi users should access the TIBCO WebFOCUS page to engage.


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