LAE

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

Originally posted by: rpcwong

I'm wondering if there is a way to do this in lavastorm.

I have two tables,
The first table is a reference table with these three important fields I need ARSCHEMANAME, FIELDNAME, ENUMID and ENUMVALUE

For example:
ARSCHEMANAME FIELDNAME ENUMID ENUMVALUE
Unavailability Status_Reason 1000 Scheduled
Unavailability Status_Reason 2000 Maintenance
Unavailability Status_Reason 3000 Out of Service
Unavailability Status_Reason 4000 Obsolete
Unavailability Status_Reason 5000 RMA - Return To Vendor
Unavailability Unavailability Class 1000 Change
Unavailability Unavailability Class 2000 Incident



The second table is the table with enumerated values that I would like to decode (into plain english) by using the reference table above.
The seond table name is Unavailability and it would have a field named "Status_Reason" with value such as 2000

Is there a way for me to join (lookup) the two tables so that for the Unavailability table, the "Status Reason" would be "Maintenance" instead of "2000".

Thanks in advance,

Rick

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

Originally posted by: ryeh

I understand what you're asking, but the example above seems to be conflicting, as 1000 and 2000 both show two different ENUMVALUE values.

What you want is something like the following (copy and paste text between ***'s into a graph):

*****************

node:Lookup
bretype:core::Lookup
editor:sortkey=5447de776e216164
input:@40fd2c746abc6dc7/=Data.40fe6c55598828e5
input:@40fd2c74486e4494/=Status_Reason.40fe6c55598828e5
output:@40fd2c7445835585/=
prop:InputKey=<<EOX
ENUMID
EOX
prop:LookupKey=<<EOX
Code
EOX
editor:XY=200,140
end:Lookup

node:Status_Reason
bretype:core::Static Data
editor:Label=Status_Reason
editor:sortkey=5447dd1b758f043b_2
output:@40fe6c55598828e5/=
prop:StaticData=<<EOX
Code,Status_Reason
1000,Scheduled
2000,Maintenance
3000,Out of Service
4000,Obsolete
5000,RMA - Return To Vendor
EOX
editor:XY=100,210
end:Status_Reason

node:Data
bretype:core::Static Data
editor:Label=Data
editor:sortkey=5447dd1b758f043b
output:@40fe6c55598828e5/=
prop:StaticData=<<EOX
ARSCHEMANAME,FIELDNAME,ENUMID
Unavailability,Status_Reason,1000
Unavailability,Status_Reason,2000
Unavailability,Status_Reason,3000
Unavailability,Status_Reason,4000
Unavailability,Status_Reason,5000
Unavailability,Unavailability Class,1000
Unavailability,Unavailability Class,2000
EOX
editor:XY=100,90
end:Data

*****************

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

Originally posted by: andycooper

When copying Roger's solution I experienced an error due to the paste function inserting some additional spaces; just in case I've attached a brg example as well for you.

Ta
Attachments:
Value Lookup.brg

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

Originally posted by: rpcwong

Hmm.. this isn't what I am trying to do..

One table would contain the fields and data like this:
ARSCHEMANAME,FIELDNAME,ENUMID,ENUMVALUE
Unavailability,Status_Reason,1000,Scheduled
Unavailability,Status_Reason,2000,Maintenance
Unavailability,Status_Reason,3000,Out of Service
Unavailability,Status_Reason,4000,Obsolete
Unavailability,Status_Reason,5000,RMA - Return To Vendor
Unavailability,Unavailability Class,1000,Change
Unavailability,Unavailability Class,2000,Incident

The 2nd table would have data like this:
CROSS_REFERENCE_ID,Status_Reason,Unavailablity Class
INC000000008760,1000,2000
INC000000010590,4000,2000
INC000000010591,2000,1000

What I want to output would be:
CROSS_REFERENCE_ID,Status_Reason,Unavailablity Class
INC000000008760,Scheduled,Incident
INC000000010590,Obsolete,Incident
INC000000010591,Maintenance,Change

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

Originally posted by: prashanth

Would something like this work? It has the same output as you requested I believe. Just copy and paste below to your BRE. Of course, you would have to remove the Static Data nodes at the beginning and connect them to your table outputs.


node:Status_Reason
bretype:core::Static Data
editor:Label=Status_Reason
editor:sortkey=5447dd1b758f043b_2
output:@40fe6c55598828e5/=
prop:StaticData=<<EOX
CROSS_REFERENCE_ID,Status_Reason,Unavailablity Class
INC000000008760,1000,2000
INC000000010590,4000,2000
INC000000010591,2000,1000
EOX
editor:XY=90,100
end:Status_Reason

node:Data
bretype:core::Static Data
editor:Label=Data
editor:sortkey=5447dd1b758f043b
output:@40fe6c55598828e5/=
prop:StaticData=<<EOX
ARSCHEMANAME,FIELDNAME,ENUMID,ENUMVALUE
Unavailability,Status_Reason,1000,Scheduled
Unavailability,Status_Reason,2000,Maintenance
Unavailability,Status_Reason,3000,Out of Service
Unavailability,Status_Reason,4000,Obsolete
Unavailability,Status_Reason,5000,RMA - Return To Vendor
Unavailability,Unavailability Class,1000,Change
Unavailability,Unavailability Class,2000,Incident
EOX
editor:XY=90,250
end:Data

node:Split
bretype:core::Split
editor:sortkey=54528daf365e1ae6
input:@40fd2c74167f1ca2/=Data.40fe6c55598828e5
output:@40fd2c7420761db6/=
output:@456df11556bd6bcf/=
prop:PredicateExpr=<<EOX
FIELDNAME!="Unavailability Class"
EOX
editor:XY=200,250
end:Split

node:Lookup
bretype:core::Lookup
editor:sortkey=5447de776e216164
input:@40fd2c746abc6dc7/=Status_Reason.40fe6c55598828e5
input:@40fd2c74486e4494/=Split.40fd2c7420761db6
output:@40fd2c7445835585/=
prop:InputKey=<<EOX
'Status_Reason'
EOX
prop:LookupKey=<<EOX
'ENUMID'
EOX
prop:Script=<<EOX
emit 1:*, '2:ENUMVALUE' as StatusReason
exclude '1:Status_Reason'

#emit 2:*
#exclude referencedFields(2,{{^LookupKey^}})

#where matchIsFound

# Note: If you want to avoid collisions with fields on the inputs
# prefix the second emit statement with the keyword "default"
# or "override".


EOX
editor:XY=260,190
end:Lookup

node:Lookup_3
bretype:core::Lookup
editor:sortkey=54528e7746953a8e
input:@40fd2c746abc6dc7/=Lookup.40fd2c7445835585
input:@40fd2c74486e4494/=Split.456df11556bd6bcf
output:@40fd2c7445835585/=
prop:InputKey=<<EOX
'Unavailablity Class'
EOX
prop:LookupKey=<<EOX
'ENUMID'
EOX
prop:Script=<<EOX
emit 1:*, '2:ENUMVALUE' as "Unavailability Class"
exclude '1:Unavailablity Class'
#emit 2:*
#exclude referencedFields(2,{{^LookupKey^}})

#where matchIsFound

# Note: If you want to avoid collisions with fields on the inputs
# prefix the second emit statement with the keyword "default"
# or "override".


EOX
editor:XY=470,190
end:Lookup_3

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

Originally posted by: rpcwong

prashanth, thanks. Your solution is very similar to what I'm doing right now.
The issue is that I have "simplified" my question by only showing 2 fields of 1 table.
Each table would have tons of fields that need to be decoded and there are many tables that would need to go through the same decoding...
I was hoping there would be a better solution so that I don't need to use "split" to repeat the same lookup or cross ref for each data field...

Any other thoughts?

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

Originally posted by: prashanth

I am not sure if you can avoid the two step process to avoid the conflicting 'ENUMID' (in this case) with any of the joins/lookup.