how to read from lookup table

[spisiakmi]

Hi, can anybody help with this task?

inputs:

"nice_date",sFaultInverter1,sFaultInverter2,sFaultInverter3,sFaultPFC,"sFaultSR-Plaus",sFaultSR,sFaultSpeed
"05.12.2023 10:46:53",0,0,1,0,"-1",0,0
"05.12.2023 10:43:27","-1","-1","-1","-1","-1","-1","-1"
"05.12.2023 10:41:17",0,320,0,0,"-1",0,0
"05.12.2023 10:30:32",0,0,1,0,"-1",0,0
"05.12.2023 10:28:51",0,0,1,0,"-1",0,0
"05.12.2023 10:28:10","-1","-1","-1","-1","-1","-1","-1"

Lookup

Attribut,Value,ErrorCode
sFaultInverter1,-1,NoCommunication
sFaultInverter1,0,noError
sFaultInverter1,1,CompressorCurrentSensorFault
sFaultInverter1,2,FactorySettings
sFaultInverter1,4,
sFaultInverter1,8,
sFaultInverter1,16,InverterBridgeTemperatureSensorFault
sFaultInverter1,32,DLTSensorFault
sFaultInverter1,64,ICLFailure
sFaultInverter1,128,EEPROMFault
sFaultInverter1,256,UpdateProcess
sFaultInverter1,512,
sFaultInverter1,1024,
sFaultInverter1,2048,
sFaultInverter1,4096,
sFaultInverter1,8129,
sFaultInverter1,16384,
sFaultInverter1,32768,
sFaultInverter2,-1,NoCommunication
sFaultInverter2,0,noError
sFaultInverter2,1,CommunicationLos
sFaultInverter2,2,DcLinkRipple
sFaultInverter2,4,
sFaultInverter2,8,AcGridOverVtg
sFaultInverter2,16,AcGridUnderVtg
sFaultInverter2,32,DcLinkOverVtgSW
sFaultInverter2,64,DcLinkUnderVtg
sFaultInverter2,128,SpeedFault
sFaultInverter2,256,AcGridPhaseLostFault
sFaultInverter2,512,InverterBridgeOverTemperature
sFaultInverter2,1024,
sFaultInverter2,2048,

I would like to have table with e.G. 3 columns:

"nice_date",sFaultInverter1,ErrorCode

"05.12.2023 10:46:53",0,noError
"05.12.2023 10:43:27","-1",NoCommunication
"05.12.2023 10:41:17",0,noError
"05.12.2023 10:30:32",0,noError
"05.12.2023 10:28:51",0,noError
"05.12.2023 10:28:10","-1",NoCommunication

for each value of sFaultInverter1 an ErrorCode from the lookUp table. Any help?

[dtburrows3 · Accepted solution]

So simply hitting the lookup with the input field "Value" as "sFaultInverter1" will pull back ErrorCodes for multiple rows from the lookup since there is multiple "Attribut" values with the same "Value". Since you are asking to pull back ErrorCodes for the "sFaultInverter1" field from the raw data I assume you just want to pull back the ErrorCodes that have the Attribut "sFaultInverter1".

This can be done a few different ways.

You can scope down the lookup inline to only pull back Attribut="sFaultInverter1" and then do a join against Value from the lookup. That would look something like this.

| join type=left sFaultInverter1
        [
            | inputlookup <lookup> where Attribut="sFaultInverter1"
                | fields - Attribut
                | rename
                    Value as sFaultInverter1
            ]
    | fields + "nice_date", sFaultInverter1, ErrorCode

Or create a temporary field and include as an additional input field to the lookup like this.

| eval
        scoped_lookup_attribute="sFaultInverter1"
    | lookup <lookup> Attribut as scoped_lookup_attribute, Value as sFaultInverter1 OUTPUT ErrorCode    
    | fields + "nice_date", sFaultInverter1, ErrorCode

 Both of these methods return the same results below

You can see that if you only use "sFaultInverter1" as the only input field to the lookup it will actually pull back multiple results since there are multiple Attribut in the lookup with the same Value. They just happen to have the same ErrorCodes in your example, but if they were different that would become problematic. And it would just take more SPL to account for the multivalue field when I don't think it is really needed for this.

[spisiakmi]

Hi dtburrows3, thank you very much. It works. I like that solution with the temporary field.

[ITWhisperer]

Try something like this

| lookup <your lookup> Value AS sFaultInverter1 OUTPUT ErrorCode
| table "nice_date", sFaultInverter1, ErrorCode