Jump to content
groovyPost Forums

Groovy Script not working


Recommended Posts

Example.xlsx

Hello together, I'm new on this forum and hope to get help with my problem:
I have given the attached table which is an SAP extract. As you can see, we have given multiple KALNR which can have more than one identical entry. Every KALNR has exactly one field in a row where VORNR is empty. It's always the case for every KALNR. WERTN shows the valuation for VORNR. 

What I want is, that the groovy script groups all rows where KALNR is identical. Then it should choose the WERTN value where we have no entry in VORNR and divides it with the quantity, where we have entries. --> Example: KALNR 100000492 has 40.29 as target value and 3 rows where VORNR is filled. --> 40.29 / 3 = 13.43. 

Then the script should add this calculated amount in a new column to the given WERTN. --> Example (same KALNR)

40.29 stays as it is
32.08 + 13.43
67.4 + 13.43
53.89 +13.43

The script is included in an external software. So this software already has the table given. No import is needed. Do you have any idea how I can get the correct result for each KALNR?

My current not working script is:

def resultData = [:]

def groupedData = [:]

for (row in input) {
    def kalnr = row.KALNR
    if (!groupedData.containsKey(kalnr)) {
        groupedData[kalnr] = []
    }
    groupedData[kalnr].add(row)
}

groupedData.each { kalnr, entries ->
    def vorCount = entries.count { it.VORNR != '' }

    def emptyVornrEntry = entries.find { it.VORNR == '' }
    def emptyVornrWert = emptyVornrEntry?.WERTN ?: 0

    def dividedWert = emptyVornrWert / vorCount

    def totalWert = entries.findAll { it.VORNR != '' }.sum { it.WERTN }

    def resultWert = totalWert + dividedWert

    resultData[kalnr] = resultWert
}

for (row in input) {
    def kalnr = row.KALNR
    if (resultData.containsKey(kalnr)) {
        row['NEW_COLUMN'] = resultData[kalnr]
    } else {
        row['NEW_COLUMN'] = null  
    }
}

return input


Thanks a lot in advance!

Link to comment
Share on other sites

Xanxus,

Sorry to say, this forum isn't really focused on Groovy Script. It's more of an accompaniment to groovyPost.com, a site covering news and how-to content for computers, smartphones, tablets, etc. While someone here might be versed in Groovy Script, you're probably going to have better luck in a forum dedicated to it.

Cheers,

Jeff

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Who's Online   0 Members, 0 Anonymous, 8 Guests (See full list)

    • There are no registered users currently online
×
×
  • Create New...