need to assign one value per group

[system]

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.

---

BOBJFan (BOB member since 2011-09-24)

[system]

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

---

jlynn73 (BOB member since 2009-10-27)

[system]

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.

---

BOBJFan (BOB member since 2011-09-24)

[system]

you create a new branch to do the ob+gb, then join that back to the original stream. (BG = group by )

---

jlynn73 (BOB member since 2009-10-27)

[system]

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

---

BOBJFan (BOB member since 2011-09-24)

[system]

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)

---

jlynn73 (BOB member since 2009-10-27)

[system]

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.

---

BOBJFan (BOB member since 2011-09-24)

[system]

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.

---

jlynn73 (BOB member since 2009-10-27)

[system]

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.

---

BOBJFan (BOB member since 2011-09-24)

[system]

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.

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

---

jlynn73 (BOB member since 2009-10-27)

[system]

You are such a Pro … Just that I need to do it in BODS only and no Python

---

BOBJFan (BOB member since 2011-09-24)

[system]

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.

password is a single space

bobj_assignKey.zip (5.0 KB)

---

jlynn73 (BOB member since 2009-10-27)

[system]

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.

---

BOBJFan (BOB member since 2011-09-24)

[system]

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.

---

jlynn73 (BOB member since 2009-10-27)

[system]

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 ()

---

BOBJFan (BOB member since 2011-09-24)

[system]

If you reverse your sortation, previous row becomes next row. I did that in my spaghetti flow to get around storing the previous row into a global variable.

When you sequence your rows, you can access the next/previous rows by joining on the sequence ±1.

---

jlynn73 (BOB member since 2009-10-27)

[system]

Ok, I will try that way, thanks!

---

BOBJFan (BOB member since 2011-09-24)

[system]

Hi jylnn73,

I tried reverse sorting but then it is failing because for example if data is ;

subkey __ numkey __ valstart
aaa __ 111 __ 21.04.2019
aaa __ 222 __ 05.05.2019
aaa __ 111 __ 31.05.2019

then if I will reverse the sortation, I will get

subkey __ numkey __ valstart __ Valend
aaa __ 111 __ 21.04.2019 __ 31.05.2019
aaa __ 222 __ 05.05.2019 __ 05.05.2019
aaa __ 111 __ 31.05.2019 __ 21.04.2019

whereas my need is ;

subkey __ numkey __ valstart __ Valend
aaa __ 111 __ 21.04.2019 __ 05.05.2019
aaa __ 222 __ 05.05.2019 __ 31.05.2019
aaa __ 111 __ 31.05.2019 __ 01.01.5555

for the last row Valend will be 01.01.5555

---

BOBJFan (BOB member since 2011-09-24)

[system]

That should work as long as you’re sorting on the generated sequence number.

if your next date is always populated, you could always join on row number +1 for all except the last row.

---

jlynn73 (BOB member since 2009-10-27)