BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

Follow BOB on Twitter! 
Follow BOB on Twitter! (Opens a new window)  

General Notice: No events within the next 45 days.

need to assign one value per group

Goto page 1, 2  Next
 
Search this topic... | Search DI: Designer and Job Design... | Search Box
Register or Login to Post    Forum Index -> Data Integrator -> DI: Designer and Job Design  Previous TopicPrint TopicNext Topic
Author Message
BOBJFan
Principal Member
Principal Member



Joined: 24 Sep 2011

Posts: 138



PostPosted: Wed Aug 21, 2019 10:14 am 
Post subject: need to assign one value per group

Hi There, I have a data as;

dt -num -subkey -assignkey
01.01.2009 - 100 - k - a
04.01.2009 - 100 - k - a
08.01.2009 - 200 - k - b
15.01.2009 - 200 - k - b
17.01.2009 - 200 - k - b
19.01.2009 - 100 - k - c
20.01.2009 - 100- k - c
03.01.2009 - 300 -j - a
05.01.2009 - 300 - j - a
06.01.2009 - 400 - p - a

What I need to do is generate assignkey based on the num and subkey.

Please can you giude me to generate this format using BODS.

Thanks in advance.
Back to top
jlynn73
Forum Associate
Forum Associate



Joined: 27 Oct 2009

Posts: 571
Location: DesMoines Iowa


flag
PostPosted: Wed Aug 21, 2019 3:22 pm 
Post subject: Re: need to assign one value per group

if you do an orderby+groupby on your group fields, then add a sequence number(gen_row_num()) ... then join that back on your group fields in another query ... walla icon_biggrin.gif
Back to top
BOBJFan
Principal Member
Principal Member



Joined: 24 Sep 2011

Posts: 138



PostPosted: Fri Aug 23, 2019 8:26 am 
Post subject: Re: need to assign one value per group

thanks for your reply, I did not understood how to join back on group fields. If you have any example to explain will be very helpful. Thanks for your help in advance.
Back to top
jlynn73
Forum Associate
Forum Associate



Joined: 27 Oct 2009

Posts: 571
Location: DesMoines Iowa


flag
PostPosted: Fri Aug 23, 2019 8:37 am 
Post subject: Re: need to assign one value per group

you create a new branch to do the ob+gb, then join that back to the original stream. (BG = group by yesnod.gif )
Back to top
BOBJFan
Principal Member
Principal Member



Joined: 24 Sep 2011

Posts: 138



PostPosted: Fri Aug 23, 2019 10:25 am 
Post subject: Re: need to assign one value per group

great thanks , it is working half way though, what I am getting is ;

dt -num -subkey -assignkey
01.01.2009 - 100 - k - a
04.01.2009 - 100 - k - a
08.01.2009 - 200 - k - b
15.01.2009 - 200 - k - b
17.01.2009 - 200 - k - b
19.01.2009 - 100 - k - c
20.01.2009 - 100- k - c
03.01.2009 - 300 -j - d - instead of a which is my requirement when new subkey begins
05.01.2009 - 300 - j - d - instead of a which is my requirement when new subkey begins
06.01.2009 - 400 - p - e - instead of a which is my requirement when new subkey begins

Hope it helps. Thanks again in advance
Back to top
jlynn73
Forum Associate
Forum Associate



Joined: 27 Oct 2009

Posts: 571
Location: DesMoines Iowa


flag
PostPosted: Fri Aug 23, 2019 12:35 pm 
Post subject: Re: need to assign one value per group

I'm not following your requirements. Does it reset back to A after C ?

realizing you're assigning an alpha instead of just a sequence, but the sequence can of course be translated. chr(65+n)
Back to top
BOBJFan
Principal Member
Principal Member



Joined: 24 Sep 2011

Posts: 138



PostPosted: Mon Aug 26, 2019 11:40 am 
Post subject: Re: need to assign one value per group

Thanks for your reply, assignkey resets when the subkey changes.when subkey changes, assignkey resets and again begins with 'a' and increments to 'b' when 'num' changes ... hope this explains my requirment.

Thanks again for your reply.
Back to top
jlynn73
Forum Associate
Forum Associate



Joined: 27 Oct 2009

Posts: 571
Location: DesMoines Iowa


flag
PostPosted: Mon Aug 26, 2019 12:11 pm 
Post subject: Re: need to assign one value per group

Ok, I mostly see what you're looking for I just dont understand the sortation.

if you did a group by on subkey,num and then did a gen_row_num_by_group(subkey,num) ... converting that to an alpha( chr(64+grbg) that would give you your assignkey.

never trust DS to preserve your sortation.
Back to top
BOBJFan
Principal Member
Principal Member



Joined: 24 Sep 2011

Posts: 138



PostPosted: Tue Aug 27, 2019 5:45 am 
Post subject: Re: need to assign one value per group

Hi I cannot use group by gen_row_num_by_group(subkey,num) because then it will group
01.01.2009 - 100 - k - a
04.01.2009 - 100 - k - a

08.01.2009 - 200 - k - b
15.01.2009 - 200 - k - b
17.01.2009 - 200 - k - b
19.01.2009 - 100 - k - c
20.01.2009 - 100- k - c


highlighted rows above (100 - k) into 1 group (100-k-a) whereas I want them to be in 2 groups (100-k-a) and (100-k-c) as per order of their appearance based on dates.
By using group by (num-subkey) it is failing my need to generate them as 2 different groups (100-k-a) and (100-k-c) . hope it helps, let me know if you didn't understood anything in here. Thanks for helping me with this query.
Back to top
jlynn73
Forum Associate
Forum Associate



Joined: 27 Oct 2009

Posts: 571
Location: DesMoines Iowa


flag
PostPosted: Tue Aug 27, 2019 8:32 am 
Post subject: Re: need to assign one value per group

Do you have access to the user defined transform?

This seems like it should be a lot easier than it is. Like if you could conditionally gen_row_by_group() and look at the previous row value.

doing it in Python would be really easy. yesnod.gif

Code:
IH = open(r'd:\desktop\testfile.txt','r')
saveSubkey  = None
assignKey   = 0
tupSave     = None
for record in IH:
    date,num,subkey,req = record.rstrip('\n').split('-')
    tupRec = (subkey,num)
    if tupSave is None:
        assignKey = 0
        tupSave = (subkey,num)
    elif tupRec != tupSave:
        assignKey += 1
        tupSave = tupRec

    if saveSubkey is None:
        saveSubkey = subkey
    elif subkey != saveSubkey:
        assignKey = 0
        saveSubkey = subkey

    print date,num,subkey,req,chr(65+assignKey)
IH.close()


>>>
01.01.2009 100 k a A
04.01.2009 100 k a A
08.01.2009 200 k b B
15.01.2009 200 k b B
17.01.2009 200 k b B
19.01.2009 100 k c C
20.01.2009 100 k c C
03.01.2009 300 j a A
05.01.2009 300 j a A
06.01.2009 400 p a A
>>>
Back to top
BOBJFan
Principal Member
Principal Member



Joined: 24 Sep 2011

Posts: 138



PostPosted: Tue Aug 27, 2019 1:12 pm 
Post subject: Re: need to assign one value per group

You are such a Pro icon_biggrin.gif .... Just that I need to do it in BODS only and no Python icon_sad.gif
Back to top
jlynn73
Forum Associate
Forum Associate



Joined: 27 Oct 2009

Posts: 571
Location: DesMoines Iowa


flag
PostPosted: Wed Aug 28, 2019 1:04 pm 
Post subject: Re: need to assign one value per group

Pretty sure this will do the trick. It gets a bit inefficient as the quantity increases but it looks to work as requested.

it uses a gen_row_num_by_group, selects off the 1st member of each group then reverses the sortation and fills in the Null assign keys with the closest sequence. Its pretty ugly. crazy.gif

password is a single space
Back to top
BOBJFan
Principal Member
Principal Member



Joined: 24 Sep 2011

Posts: 138



PostPosted: Thu Aug 29, 2019 10:55 am 
Post subject: Re: need to assign one value per group

Hey, thanks for the code ! I have pretty much achieved it by using custom function and by defining Parameter at DF level to access previous row value.
Back to top
jlynn73
Forum Associate
Forum Associate



Joined: 27 Oct 2009

Posts: 571
Location: DesMoines Iowa


flag
PostPosted: Tue Sep 03, 2019 8:55 am 
Post subject: Re: need to assign one value per group

Congrats!! I tried using the previous row but forgot about assigning it to a variable. It returns null on the second subordinate record, which led me to my spaghetti dataflow.
Back to top
BOBJFan
Principal Member
Principal Member



Joined: 24 Sep 2011

Posts: 138



PostPosted: Tue Sep 03, 2019 10:41 am 
Post subject: Re: need to assign one value per group

Yeah, thanks for your suggestions, it helped me to progress, great relief it got sorted and hence I landed on another query. Now I need to access next row value and BODS does not provide any function for next row like it has for Previous_Row_Value () icon_sad.gif
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Data Integrator -> DI: Designer and Job Design  Previous TopicPrint TopicNext Topic
Page 1 of 2 All times are GMT - 5 Hours
Goto page 1, 2  Next
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0304 seconds using 18 queries. (SQL 0.0032 Parse 0.0010 Other 0.0262)
CCBot/2.0 (https://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo