B2B Integration

Is there a way to extract all matched records without using EXTRACT() function.

We have to merge two huge files and map out the merged output. We implemented the scenario using EXTRACT function but it is taking a long time to execute because it does sequential search for all records.

We also tried SORTUPBY() and SEARCHUP() , But SEARCHUP() is returning only one matched record. We need to return all matched records in this case.

Is there any way to map all the matched records using sort/search functions?

File1: (500mb)

abc|123|somedata1
def|456|somedata2
hij|789|somedata3

File2: (100mb)

456|date4
456|date5
789|date6
123|date1
123|date2
123|date3

Desired Output

abc|123|somedata1|date1
abc|123|somedata1|date2
abc|123|somedata1|date3
def|456|somedata2|date4
def|456|somedata2|date5
hij|789|somedata3|date6

------------------------------
Kishore Reddy
------------------------------

#B2BIntegration
#SupplyChain

I would think you could loop through every line of File2, and then do a SEARCHUP() and grab the single record from File1 that matches.

Am I missing something?

Thank you.

------------------------------
Paul Brett
 
IBM Sterling Transformation Extender (ITX) Client Support
------------------------------

Original Message:
Sent: Thu May 14, 2020 08:59 PM
From: Kishore Reddy
Subject: ITX - EXTRACT all matched records using SORT

Is there a way to extract all matched records without using EXTRACT() function.

We have to merge two huge files and map out the merged output. We implemented the scenario using EXTRACT function but it is taking a long time to execute because it does sequential search for all records.

We also tried SORTUPBY() and SEARCHUP() , But SEARCHUP() is returning only one matched record. We need to return all matched records in this case.

Is there any way to map all the matched records using sort/search functions?

File1: (500mb)

abc|123|somedata1
def|456|somedata2
hij|789|somedata3

File2: (100mb)

456|date4
456|date5
789|date6
123|date1
123|date2
123|date3

Desired Output

abc|123|somedata1|date1
abc|123|somedata1|date2
abc|123|somedata1|date3
def|456|somedata2|date4
def|456|somedata2|date5
hij|789|somedata3|date6

------------------------------
Kishore Reddy
------------------------------

#B2BIntegration
#SupplyChain

One could elaborate on your proposal, using Ctrl-break logic so that lines in the second file become "blocks", thus limiting the calls to SEARCHUP

------------------------------
Laurent Barthelemy
Technical director
Satisco
mont saint guibert
+33 616792115
------------------------------

Original Message:
Sent: Fri May 15, 2020 07:47 AM
From: Paul Brett
Subject: ITX - EXTRACT all matched records using SORT

I would think you could loop through every line of File2, and then do a SEARCHUP() and grab the single record from File1 that matches.

Am I missing something?

Thank you.

------------------------------
Paul Brett
 
IBM Sterling Transformation Extender (ITX) Client Support

Original Message:
Sent: Thu May 14, 2020 08:59 PM
From: Kishore Reddy
Subject: ITX - EXTRACT all matched records using SORT

Is there a way to extract all matched records without using EXTRACT() function.

We have to merge two huge files and map out the merged output. We implemented the scenario using EXTRACT function but it is taking a long time to execute because it does sequential search for all records.

We also tried SORTUPBY() and SEARCHUP() , But SEARCHUP() is returning only one matched record. We need to return all matched records in this case.

Is there any way to map all the matched records using sort/search functions?

File1: (500mb)

abc|123|somedata1
def|456|somedata2
hij|789|somedata3

File2: (100mb)

456|date4
456|date5
789|date6
123|date1
123|date2
123|date3

Desired Output

abc|123|somedata1|date1
abc|123|somedata1|date2
abc|123|somedata1|date3
def|456|somedata2|date4
def|456|somedata2|date5
hij|789|somedata3|date6

------------------------------
Kishore Reddy
------------------------------

#B2BIntegration
#SupplyChain

We implemented it using the "blocks" concept, and the map is completing in <20 mins now. Here is the logic.

OutputCard1 :

• Sort File2 by ID column

OutputCard2 :

• Call a RUN map, Pass OutputCard1's data as input to the map
  • RUN Map Input Typtree Structure
    • File
      • GROUPBY_ID (0:S)
        • DETAIL (0:S)
          • ID
          • Date
• Group all ID records in the RUN map by using a component rule on the DETAIL Group
  • DETAIL (0:S) ===> ID Field:$ = ID Field:$ [LAST]
• Map Output to a "blocked" structure
  • RUN Map Output Typetree Structure
    • File
      • GROUPEDBY_ID (0:S)
        • ID
        • DETAIL (0:S)
          • ID
          • Date
            

OutputCard3:

• Parse data returned by RUN map by using PARSE() function
• Parse to the same structure as RUN Map's Output card

OutputCard4:

• Create merged file 
• Output Card4 structure
•  File
  • GROUPBY_ID (0:S)  <== Use SEARCHUP(GROUPEDBY_ID, ID:GROUPEDBY_ID:File2, ID:File1 ) function in a functional map to extract ALL                                                    matching records of the ID in File1
    • DETAIL (0:S)  <== Loop through all matched records and map to the output structure
      • NAME
      • ID
      • SOMEDATA
      • DATE
  • We also used EITHER (SEARCHUP(), "<CR><LF>") to map out records in File1 that did not have a match in File2. 

------------------------------
Kishore Reddy
------------------------------

Original Message:
Sent: Mon May 18, 2020 08:45 AM
From: Laurent Barthelemy
Subject: ITX - EXTRACT all matched records using SORT

One could elaborate on your proposal, using Ctrl-break logic so that lines in the second file become "blocks", thus limiting the calls to SEARCHUP

------------------------------
Laurent Barthelemy
Technical director
Satisco
mont saint guibert
+33 616792115

Original Message:
Sent: Fri May 15, 2020 07:47 AM
From: Paul Brett
Subject: ITX - EXTRACT all matched records using SORT

I would think you could loop through every line of File2, and then do a SEARCHUP() and grab the single record from File1 that matches.

Am I missing something?

Thank you.

------------------------------
Paul Brett
 
IBM Sterling Transformation Extender (ITX) Client Support

Original Message:
Sent: Thu May 14, 2020 08:59 PM
From: Kishore Reddy
Subject: ITX - EXTRACT all matched records using SORT

Is there a way to extract all matched records without using EXTRACT() function.

We have to merge two huge files and map out the merged output. We implemented the scenario using EXTRACT function but it is taking a long time to execute because it does sequential search for all records.

We also tried SORTUPBY() and SEARCHUP() , But SEARCHUP() is returning only one matched record. We need to return all matched records in this case.

Is there any way to map all the matched records using sort/search functions?

File1: (500mb)

abc|123|somedata1
def|456|somedata2
hij|789|somedata3

File2: (100mb)

456|date4
456|date5
789|date6
123|date1
123|date2
123|date3

Desired Output

abc|123|somedata1|date1
abc|123|somedata1|date2
abc|123|somedata1|date3
def|456|somedata2|date4
def|456|somedata2|date5
hij|789|somedata3|date6

------------------------------
Kishore Reddy
------------------------------

#SupplyChain
#B2BIntegration