LAE

Welcome to the LAE community!  Please feel free to start a discussion in the discussion tab or join in a conversation.

Discussions

Members

Resources

Events

 View Only

  • 1.  data contains min and max values, how to create an incrementing list within range

    Employee
    Posted 06-02-2017 01:57

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: heillandi

    Hi,

    I am having the following situation:

    Table 1 has columns "Value from" and "Value to" , so each row is specifying a certain range of account numbers and a name for this account group.
    Table 2 has a plain list of account numbers existing.

    I'm wondering how can I "expand" each row of Table 1 adding the numbers within the range as new rows, i.e. if I have a following entry for accounts between 10000-10100


    #name1 --- 10000 --- 10100

    then I need to generate 100 new entries looking like this:

    #name1 --- 10000 -- 10100 --- 10000
    #name1 --- 10000 -- 10100 --- 10001
    #name1 --- 10000 -- 10100 --- 10002
    ...
    #name1 --- 10000 -- 10100 --- 10099
    #name1 --- 10000 -- 10100 --- 10100


    Alternatively, how can I create a "nested loop" which picks a row from Table 1, goes through Table 2 and gets the values found there for the specified range; moves to the next row of Table 1 and goes again through Table 2, and so until the end of Table 1..

    I understand "logically" how a piece of code should work but don't know which node to put it to.. I tried to put emit function inside the "while" loop (within Filter node), but it didn't work...


  • 2.  RE: data contains min and max values, how to create an incrementing list within range

    Employee
    Posted 06-02-2017 08:02

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: stonysmith

    There are two answers.

    The first answer is what you attempted.. with the "while" operator, but you have to wrap the emit inside of a "do output" operator.

    node:Expand_Ranges
bretype:core::Filter
editor:Label=Expand Ranges
editor:sortkey=593178f42dda35d0
input:@40fd2c74167f1ca2/=Static_Data.40fe6c55598828e5
output:@40fd2c7420761db6/=
prop:Script=<<EOX
output 1 {
emit Name
emit long(0) as Account
where false   #wait till told to write a record
}
a=Start
while a < End {
 do output 1 {emit a as Account}
 a=a+1
 }
EOX
editor:XY=310,100
end:Expand_Ranges

node:Static_Data
bretype:core::Static Data
editor:sortkey=593178b319f67041
output:@40fe6c55598828e5/=
prop:StaticData=<<EOX
Name,Start:long,End:long
#name1,10000,10100
#name2,20300,20359
EOX
editor:XY=180,100
end:Static_Data
    The second method is to use a JOIN node and match all record to all records.. but it is extremely slow on large datasets and may even run out of memory on 'moderate' datasets.

    node:Soiurce_Ranges
bretype:core::Static Data
editor:Label=Soiurce Ranges
editor:sortkey=593178b319f67041
output:@40fe6c55598828e5/=
prop:StaticData=<<EOX
Name,Start:long,End:long
#name1,10000,10100
#name2,20300,20359
EOX
editor:XY=180,200
end:Soiurce_Ranges

node:Join
bretype:core::Join
editor:sortkey=59317b8666d875e9
input:@40fd2c745b6d7704/=Soiurce_Ranges.40fe6c55598828e5
input:@40fd2c74504921cd/=Account_without_Names.40fe6c55598828e5
output:@40fd2c7430f76546/=
prop:JoinType=i
prop:LeftInputKey=<<EOX
1
EOX
prop:RightInputKey=<<EOX
1
EOX
prop:Script=<<EOX
emit Name
emit Account
where join.match and (Account >= Start and Account <= End)
EOX
editor:XY=310,200
editor:Notes=This version does a straight up cartesian join, testing every left record to every right record
editor:Notes=It will fail if either input is larger than available RAM memory
editor:Notes=
end:Join

node:XRef
bretype:core::X-Ref
editor:sortkey=59317c20121c6ba3
input:@45781ca80c2802d0/=Soiurce_Ranges.40fe6c55598828e5
input:@45781ca971fe502f/=Account_without_Names.40fe6c55598828e5
output:@45781cad02e051b0/=
output:@457826825d7c2de0/=
output:@4578268450ad5d88/=
prop:LeftInputKey=<<EOX
long(Start/100)
EOX
prop:RightInputKey=<<EOX
long(Account/100)
EOX
prop:Script=<<EOX
#disable output 1
#output 1 {
#    emit 1:*
#    where join.leftOrphan
#}

output 2 {
    emit *
    exclude referencedFields(2,{{^RightInputKey^}})
    where join.match and (Account >= Start and Account <= End)
}

#disable output 3
#output 3 {
#    emit 2:*
#    where join.rightOrphan
#}
EOX
prop:SortLeftInput=true
prop:SortRightInput=true
editor:XY=310,290
editor:Notes=This version does a partial cartesian join, testing groups of 100 left records to groups of 100 right records
editor:Notes=It may still fail if either input is larger than available RAM memory
editor:Notes=
node:Sort_2
bretype:::Sort
editor:shadow=45782670131b71e8
input:@40fd2c743ebf4304/=
output:@40fd2c746a2a3b47/=
end:Sort_2

node:Bypass_3
bretype:::Bypass
editor:shadow=4578267017753cec
input:@45782010749f0c96/=
input:@457820124fef7658/=
output:@40fd2c7436717256/=
end:Bypass_3

node:Join
bretype:::Join
editor:shadow=457826701862249e
input:@40fd2c745b6d7704/=
input:@40fd2c74504921cd/=
output:@40fd2c7430f76546/=
output:@457826cc03a465f4/=
output:@457826cc510c69e0/=
end:Join

node:Bypass
bretype:::Bypass
editor:shadow=4578267041681261
input:@45782010749f0c96/=
input:@457820124fef7658/=
output:@40fd2c7436717256/=
end:Bypass

node:Sort
bretype:::Sort
editor:shadow=4578267075a60d09
input:@40fd2c743ebf4304/=
output:@40fd2c746a2a3b47/=
end:Sort

end:XRef

node:Account_without_Names
bretype:core::Static Data
editor:Label=Account without Names
editor:sortkey=59317b5e151f254d
output:@40fe6c55598828e5/=
prop:StaticData=<<EOX
Account:long
10010
10015
10016
10026
10031
20302
20306
20310
EOX
editor:XY=180,300
end:Account_without_Names


  • 3.  RE: data contains min and max values, how to create an incrementing list within range

    Employee
    Posted 06-12-2017 23:47

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: heillandi

    Thank you very much @stonysmith.
    I used the 1st method and it worked for me: to generate a list of all possible values and then to join with the master data table to leave only those which are actually existing.