LAE

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

Originally posted by: zon

Hi,

I have a issue with node lookup.
I have a voip calls rating simulator in MDA!

I have two tables:
> one with all calls from our customers
> one with the products from our customers (for looking up and determine the product for the call). This table has the Product ID, Customer ID and the start and end date of the product. In these table I have all products except the product that our customers have the most. For customers that had changed product during the month, I will have, for example, two lines in these file.

What I'm trying to do in the lookup is: separate the calls that were made by the product that is not in the other table from the ones that were made by the products in the second table.
But, for some reason, the node is not doing the separation correctly.

I'm sending you attached the files and the script nodes, so you can re-create what I'm doing and try to understand what I'm doing wrong. The key for the lookup node is "BAINST_ID".
(Sorry, I'm enable to upload the attach. Can you send me an email requesting them, and I will send to you: natanael.l.moreira@zon.pt)

Best Regards and thanks in advance.

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

Originally posted by: rboccuzzi

When you do a lookup on a value, it loads the lookup value from the second input into memory, and keeps one row if there are duplicate values. You can't make a decision as to which row in your lookup gets matched via a script operation. In order to do this, you would need to use a join to create all possible matches, and then a filter, to discard the ones that are not correct. Please note, this can create a memory issue as all records that match products will be loaded into memory, which could exhaust your memory. A better node to use will be the fuzzy matching node that we are about to release in the next month or so. I have attached what could work, depending upon your volume of records and other constraints I may not be aware of (for example, I assume that for a given product, there will be no overlapping range).

Cheers
Rich
Attachments:
Example Range Lookup.brg

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

Originally posted by: zon

Hi Rich,

Thanks for your help.

By the way, just one doubt: why you created the node "Modify Field Prefix"? Is it really needed?

Cheers,
Natanael
ZON

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

Originally posted by: rboccuzzi

It isn't really needed, but it allowed me in a later node to do a exclude wildcard "prefix.*" for those records where that data wasn't relevant (it didn't match). So just a shortcut.

Cheers
Rich

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

Originally posted by: zon

Well, I've tried to run the process you built, but it turn out error saying "exceeded buffer size".

So, I need to wait untill you release that new node, the fuzzy match.
Will I be able to just add that node to my LAE? How do I get that node, onde is released and how do I add to my LAE?

Cheers,
ZON
Natanael Moreira

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

Originally posted by: rboccuzzi

So it is released now, and is available from our website. If you go to www.lavastorm.com and click on Resources, then Software Downloads and Trials, you will see the Lavastorm Analytics Library and Enhanced Analytics pack. This is what you want to download and install, which will give you another base library to include. When you start a new graph or are editing a graph and want to use these nodes, you need to (per the release notes for the Enhanced Analytics Pack) edit base libraries, and add "lal1", to get access to these new nodes.

I modified the above graph so it is using the fuzzy matching, that should work. I left the old join in, for illustration, but you can just delete that.

Cheers
Rich
Attachments:
Example Range Lookup.brg

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

Originally posted by: zon

Hi Rich,

Thanks for your answer. I've been trying to download that pack from your site, but I just can't. Once I click in the link, it starts thinking, thinking, but it doesn't start downloading.
Can you help?

Cheers,
Natanael

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

Originally posted by: rboccuzzi

Just making sure, you are clicking this link to the node pack? Is there a banner that is appearing on top of your browser or a pop-up blocker or virus software that is preventing it? Usually this is the case, as you are downloading a .exe, and something might be asking your permission.

Cheers
Rich

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

Originally posted by: zon

Now is asking me for user name and password.
Is it supposed to?

Cheers,
Natanael

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

Originally posted by: rboccuzzi

It should not be asking you for a username or password. Are you clicking the above link I sent you? Which browser are you using?

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

Originally posted by: zon

Hi,

I already downloaded the info and installed it. I've tried to run it, but it turned out error. Here is the error:

++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++++
-- Errors for Fuzzy_Join_Engine
2012-06-20 22:10:31.628; ERROR: [dbParam] [ClassNotFoundException]
id: 10 chain: 0 group: 0
javaExceptionDetail: java.lang.ClassNotFoundException: 'org.hsqldb.jdbc.JDBCDriver';
stack trace:
java.net.URLClassLoader$1.run(URLClassLoader.java@ 202)
java.security.AccessController.doPrivileged(Access Controller.java@-2)
java.net.URLClassLoader.findClass(URLClassLoader.j ava@190)
java.lang.ClassLoader.loadClass(ClassLoader.java@3 07)
java.lang.ClassLoader.loadClass(ClassLoader.java@2 48)
java.lang.Class.forName0(Class.java@-2)
java.lang.Class.forName(Class.java@169)
com.lavastorm.brain.node.fuzzyJoin.BaseDatabaseEng ine.dbParam(BaseDatabaseEngine.java@357)
com.lavastorm.brain.node.fuzzyJoin.FuzzyJoin.proce ssAll(FuzzyJoin.java@204)
com.lavastorm.brain.node.NodeWrapper.nodeProcess(N odeWrapper.java@407)
com.lavastorm.brain.node.NodeWrapper.run(NodeWrapp er.java@598)
com.lavastorm.brain.node.NodeWrapper.main(NodeWrap per.java@1122)
javaDetail: com.lavastorm.brain.node.fuzzyJoin.BaseDatabaseEng ine.dbParam(BaseDatabaseEngine.java@373)
dataDetail:
inRight(in) row: 0
columns: ['TABLERIGHT_ID', 'java.lang.Long'], ['_FMKeyRight', 'java.lang.String'], ['_FMValueRight', 'java.lang.String'], ['prod.ID', 'java.lang.String'], ['prod.BAInst_ID', 'java.lang.String'], ['prod.Inicio_de_Portfolio', 'com.lavastorm.lang.Date'], ['prod.Fim_de_Portfolio', 'com.lavastorm.lang.Date'], ['prod.COUNT', 'java.lang.Integer']
rightorphan(out)
leftorphan(out)
multimatch(out)
inLeft(in) row: 0
columns: ['TABLELEFT_ID', 'java.lang.Long'], ['_FMKeyLeft', 'java.lang.String'], ['_FMValueLeft', 'java.lang.String'], ['PRODUCT_INSTANCE_ID', 'java.lang.String'], ['BAINST_ID', 'java.lang.String'], ['RECORD_ID', 'java.lang.String'], ['EVENT_STARTDATETIME', 'com.lavastorm.lang.TimeStamp'], ['EVENT_START_DATE', 'com.lavastorm.lang.Date'], ['EVENT_START_YEAR', 'java.lang.Integer'], ['EVENT_START_MONTH', 'java.lang.Integer'], ['EVENT_START_DAY', 'java.lang.Integer'], ['EVENT_START_TIME', 'com.lavastorm.lang.Time'], ['EVENT_START_HOUR', 'java.lang.Integer'], ['EVENT_START_MINUTE', 'java.lang.Integer'], ['EVENT_START_SECOND', 'java.lang.Integer'], ['EVENT_END_DAY', 'java.lang.Integer'], ['EVENT_END_HOUR', 'java.lang.Integer'], ['EVENT_END_MINUTE', 'java.lang.Integer'], ['EVENT_END_SECOND', 'java.lang.Integer'], ['EVENT_END_DATE', 'com.lavastorm.lang.TimeStamp'], ['BA', 'java.lang.String'], ['ORIGIN', 'java.lang.String'], ['ORIGIN_COST_CODE', 'java.lang.String'], ['DESTINATION', 'java.lang.String'], ['COST_CODE', 'java.lang.String'], ['DURATION', 'java.lang.Integer'], ['OPERATOR', 'java.lang.String'], ['HOLIDAY', 'com.lavastorm.lang.Date'], ['RowId', 'java.lang.Long']
uniquematch(out)

2012-06-20 22:10:31.644; ERROR: Node failed.
id: 12 chain: 11 group: 0
javaDetail: com.lavastorm.brain.node.NodeWrapper.run(NodeWrapp er.java@623)
++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++++++++++++++++++++++++++++++++++

Can you help me?

Cheers,
Natanael

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

Originally posted by: Tim Meagher

Hey,

See this post for information on resolving that error: http://community.lavastorm.com/showt...ghlight=hsqldb
Basically, for the LAE version you are using, hsql (required by the Fuzzy Join nodes) is not part of the installation & needs to be installed separately.

Tim.

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

Originally posted by: zon

Hi Tim,

I saw it and done it. And after re-run it, still turs out error:
+++++++++++++++++++++++++++++++++++++++
-- Errors for Fuzzy_Join_Engine
2012-06-21 12:14:36.621; ERROR: [dbStatementExecute] [SQLException]
id: 3 chain: 0 group: 0
javaExceptionDetail: java.sql.SQLException: 'Data File size limit is reached in statement [CREATE INDEX TABLELEFT_idx ON TABLELEFT ( TABLELEFT0)]';
stack trace:
org.hsqldb.jdbc.Util.sqlException()
org.hsqldb.jdbc.Util.sqlException()
org.hsqldb.jdbc.JDBCStatement.fetchResult()
org.hsqldb.jdbc.JDBCStatement.execute()
com.lavastorm.brain.node.fuzzyJoin.BaseDatabaseEng ine.dbStatementExecute(BaseDatabaseEngine.java@491 )
com.lavastorm.brain.node.fuzzyJoin.BaseDatabaseEng ine.dbTableIndex(BaseDatabaseEngine.java@388)
com.lavastorm.brain.node.fuzzyJoin.FuzzyJoin.proce ssAll(FuzzyJoin.java@223)
com.lavastorm.brain.node.NodeWrapper.nodeProcess(N odeWrapper.java@407)
com.lavastorm.brain.node.NodeWrapper.run(NodeWrapp er.java@598)
com.lavastorm.brain.node.NodeWrapper.main(NodeWrap per.java@1122)
javaDetail: com.lavastorm.brain.node.fuzzyJoin.BaseDatabaseEng ine.dbStatementExecute(BaseDatabaseEngine.java@495 )
dataDetail:
inRight(in) row: 142030
columns: ['TABLERIGHT_ID', 'java.lang.Long'], ['_FMKeyRight', 'java.lang.String'], ['_FMValueRight', 'java.lang.String'], ['prod.ID', 'java.lang.String'], ['prod.BAInst_ID', 'java.lang.String'], ['prod.Inicio_de_Portfolio', 'com.lavastorm.lang.Date'], ['prod.Fim_de_Portfolio', 'com.lavastorm.lang.Date'], ['prod.COUNT', 'java.lang.Integer']
records:
[Null, Null, Null, Null, Null, Null, Null, Null]
rightorphan(out)
leftorphan(out)
multimatch(out)
inLeft(in) row: 28677808
columns: ['TABLELEFT_ID', 'java.lang.Long'], ['_FMKeyLeft', 'java.lang.String'], ['_FMValueLeft', 'java.lang.String'], ['PRODUCT_INSTANCE_ID', 'java.lang.String'], ['BAINST_ID', 'java.lang.String'], ['RECORD_ID', 'java.lang.String'], ['EVENT_STARTDATETIME', 'com.lavastorm.lang.TimeStamp'], ['EVENT_START_DATE', 'com.lavastorm.lang.Date'], ['EVENT_START_YEAR', 'java.lang.Integer'], ['EVENT_START_MONTH', 'java.lang.Integer'], ['EVENT_START_DAY', 'java.lang.Integer'], ['EVENT_START_TIME', 'com.lavastorm.lang.Time'], ['EVENT_START_HOUR', 'java.lang.Integer'], ['EVENT_START_MINUTE', 'java.lang.Integer'], ['EVENT_START_SECOND', 'java.lang.Integer'], ['EVENT_END_DAY', 'java.lang.Integer'], ['EVENT_END_HOUR', 'java.lang.Integer'], ['EVENT_END_MINUTE', 'java.lang.Integer'], ['EVENT_END_SECOND', 'java.lang.Integer'], ['EVENT_END_DATE', 'com.lavastorm.lang.TimeStamp'], ['BA', 'java.lang.String'], ['ORIGIN', 'java.lang.String'], ['ORIGIN_COST_CODE', 'java.lang.String'], ['DESTINATION', 'java.lang.String'], ['COST_CODE', 'java.lang.String'], ['DURATION', 'java.lang.Integer'], ['OPERATOR', 'java.lang.String'], ['HOLIDAY', 'com.lavastorm.lang.Date'], ['RowId', 'java.lang.Long']
records:
[Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null]
uniquematch(out)

2012-06-21 12:14:40.215; ERROR: Node failed.
id: 5 chain: 4 group: 0
javaDetail: com.lavastorm.brain.node.NodeWrapper.run(NodeWrapp er.java@623)
+++++++++++++++++++++++++++++++++++++++++++++

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

Originally posted by: Tim Meagher

Hi,

Can you post your configured node, with an example record from each of the inputs to this node?

Are there a lot of records with null key fields in both inputs in the data?
Also, how many matches are you expecting to see in this data set?
And how many records are on each input? (from the looks of the log, there appear to be at least 28 million on the left input & 140,000 on the right input).

Also, within your graph, if you select View->Dependent Libraries, what version of lal1 is shown?

At this stage I would guess that the problem is because of too many matches on a particular row of one of the inputs, which is then causing the hsql cache file to grow too large.
However, more investigation is needed to confirm this.
It does appear that with a change to the Fuzzy Join node in lal1, we could allow this cache to be larger, however, it doesn't appear that this can be unlimited.



Tim.

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

Originally posted by: zon

Hi,

Your collegue that was previously answering, I already sent to him a sample and is posted in a previous "Reply" of him.

There are no null key fields, in both sources.
About how many records, you are right.

The version of lal1 is "lal1.brg - v1.0.1".

Well, you say "too many matches". The analysis that I'm trying to do is determined for each call, what was the tariff to be applied. Calls start time has hours, minutes and seconds. In the reference table, I only have day, month and year. Could it be that?

Cheers,
Natanael

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

Originally posted by: Tim Meagher

Hi,

I think I've managed to get a hold of the files that you were talking about - however this looks like it is from the configured lookup node, not the configured fuzzy join node which is causing the problems.
Are you able to send through a copy of your configured Fuzzy Join node?
Just select the node, CTRL + C, then paste the text into notepad & upload here.

Thanks,
Tim.

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

Originally posted by: zon

Hi Tim,

Sorry for the delay. Here it is:

node:Fuzzy_XRef
bretype:lal1::Fuzzy X-Ref
editor:sortkey=4fba4c8218f75d03
input:@4e82063a6c804dde/=Add_unique_RowId.40fd2c7420761db6
input:@4e8206386418634f/=Modify_Field_Prefix.454a22dc003534d6
output:@4ea192802c912acc/=
output:@4ea1927e296d6493/=
output:@4f14a9c40dec6740/=
output:@4f14a9c05f6e1be5/=
prop:LeftExactExpr=<<EOX
'BAInst_ID'
EOX
prop:RightExactExpr=<<EOX
'prod.BAInst_ID'
EOX
editor:XY=400,210
node:Filter
bretype:::Filter
editor:shadow=4f593fd6535349f5
input:@40fd2c74167f1ca2/=
output:@40fd2c7420761db6/=
end:Filter

node:Filter_3
bretype:::Filter
editor:shadow=4f5130e62fd56eca
input:@40fd2c74167f1ca2/=
output:@40fd2c7420761db6/=
end:Filter_3

node:Fuzzy_Join_Engine
bretype:::Fuzzy Join Engine
editor:shadow=4f5130e65821526b
input:@4eb2a8636883157c/=
input:@4eb2a8637e270cac/=
output:@4ea58d495cf126c0/=
output:@4ea58d4715ec7f49/=
output:@4f14aaca1bff0ec0/=
output:@4f14aad049ef4ab9/=
end:Fuzzy_Join_Engine

end:Fuzzy_XRef



Cheers,
Natanael Moreira

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

Originally posted by: Tim Meagher

Hi,

I notice that you don't have any of the Fuzzy key parameters set.
In which case, you should just use a normal X-Ref node.
Have you tried using this?

I believe that the Fuzzy Join node has some memory restrictions on key values that don't apply on the X-Ref node.
Some of these restrictions are still being fully investigated/addressed and a subsequent version of the LAL library will have updates - however for now, it seems like an X-Ref node would be your best option.

Tim.

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

Originally posted by: zon

Hi Tim,

Can I, using the X-Ref, found a match, not only using an ID, but also the start time of the call, along with the start and end of the product?
I besides that, in the ref table, I might have the same ID, more than once, but with different start and end dates.

Is that a problem, while using X-Ref?
The logic that I have is this:
################################
output 1 {
emit 1:*
where isNull(2:BAInst_ID) or (isNotNull(2:BAInst_ID) and (1:EVENT_STARTDATETIME.str().left(10).date("CCYY-MM-DD")<2:Inicio_de_Portfolio or
1:EVENT_STARTDATETIME.str().left(10).date("CCYY-MM-DD")>2:Fim_de_Portfolio))
}

output 2 {
emit 1:*,2:ID as ID_PROD,2:Inicio_de_Portfolio,2:Fim_de_Portfolio,2 :COUNT
exclude referencedFields(2,{{^LookupKey^}})
where isNotNull(2:BAInst_ID) and 1:EVENT_STARTDATETIME.str().left(10).date("CCYY-MM-DD")>=2:Inicio_de_Portfolio and
1:EVENT_STARTDATETIME.str().left(10).date("CCYY-MM-DD")<=2:Fim_de_Portfolio
}
#################################

Cheers,
Natanael
ZON

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

Originally posted by: johnpelz

Hi Natanael,

This can get tricky depending on a few factors (volume of data, overlap of date ranges, etc). Would you mind posting a sample set of the data & the current X-Ref node you are using? That will allow us to respond with a more targeted solution as the problem set you describe is a bit unclear.

Thanks
-John

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

Originally posted by: zon

Hi John,

Attach I'm sending two samples: one with calls (Test_Calls), another with customer product info (Test_Products).
I'm not using the X-Ref, I'm using a lookup node.
The input key is file Test_Calls; the Lookupkey is file Test_Products. In both, the key is BAINST_ID.

This is the lookup code:
output 1 {
emit 1:*
where isNull(2:BAInst_ID) or (isNotNull(2:BAInst_ID) and (1:EVENT_STARTDATETIME.str().left(10).date("CCYY-MM-DD")<2:DAT_PRQ_INI_PORT or
1:EVENT_STARTDATETIME.str().left(10).date("CCYY-MM-DD")>2:DAT_PRQ_FIM_PORT))
}

output 2 {
emit 1:*,2:COD_COMP as ID_PROD,2:DAT_PRQ_INI_PORT,2:DAT_PRQ_FIM_PORT
exclude referencedFields(2,{{^LookupKey^}})
where isNotNull(2:BAINST_ID) and 1:EVENT_STARTDATETIME.str().left(10).date("CCYY-MM-DD")>=2:DAT_PRQ_INI_PORT and
1:EVENT_STARTDATETIME.str().left(10).date("CCYY-MM-DD")<=2:DAT_PRQ_FIM_PORT
}


Regards,
Natanael
Attachments:
11-Nov.zip

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

Originally posted by: rboccuzzi

Nathaniel, currently you are using a Lookup Node, but you have duplicate BASINST_ID keys on the lookup data (second pin), which doesn't work. A Lookup Node will only use ONE (randomly chosen) of the rows that has a matching ID, and not all. (In 4.6, those duplicates will cause the Lookup Node to error, actually).

It seems like, with what you want to do, you would want to use a FUZZY MATCH type node, but it wasn't clear from the previous posts and the current data exactly why the fuzzy match node didn't work. Have you tried that with the latest version? The previous post you had with the fuzzy match node filled out doesn't quite match the data you just posted, but I created an example graph that I think is doing what you want...it matches based on ID, and then does fuzzy based on the date.

Cheers
Rich
Attachments:
FuzzyMatch.brg