Originally posted by: Bragger

Hi,

We have below requirement:

The source of our input file is database and target is fixed length output file. We need to fetch 15 different columns from database and map it to 10 fields. If there is data received in all the 15 columns then we will map the first 10 columns from input and ignore the remaining 5 columns (11th to 15th). However if there are spaces returned in any of the column then we need to shift the values and map it to the available Output fields. I will take an example to explain this:

Let's say the dblookup is returning the below 15 values from database, so my input look like this:

1.0|2.0|3.0|4.0|5|6.0|7|8|9.0|10.0|11.0|12.0|13.0|14.0|15.0

In this case, I am mapping the first available 10 values to Output along with some pointer value which is assigned to each code from input. However now let's say the DBlookup returns the value as below:

|||4.0|5|6.0||8||10.0|11.0|12.0|13.0||15.0 (total 9 non blank values)

In this case, my Output should ignore the blanks received and map these values to total 9 fields in Output and last field should be filled with blanks to make it fixed length record:

4.0 5  6.0 8 10.0 11.0 12.0 13.0 15.0 <SP><SP>

I have developed a map but facing 2 problems:

1. My functional map is repeated 10 times along with clone function. So if more than 10 non blanks values are received (as in second scenario above) then I am not able to map them. I can-not increase occurrences of functional map to "15" because that will alter the total length of record. Also increasing the counter of clone variable will not work since total occurrence of functional map is limited to 10.

2. Not able to retain the fixed length record but if values are shifted from input then at the end there are no spaces in remaining fields.

Can anyone please take a look at my map and provide the inputs?

For the sake of simplicity I have assumed my input as "|' delimited (since this is the format which will be returned by DBLOOKUP function).

Originally posted by: PaulBrett

Please see my example attached.

Input data:

1.0|2.0|3.0|4.0|5|6.0|7|8|9.0|10.0|11.0|12.0|13.0|14.0|15.0
|||4.0|5|6.0||8||10.0|11.0|12.0|13.0||15.0
1.0|||4.0|5|6.0||8||10.0|11.0|12.0|13.0|14.0|

Output data:

1.0 2.0 3.0 4.0 5   6.0 7   8   9.0 10.0
4.0 5   6.0 8   10.011.012.013.015.0    
1.0 4.0 5   6.0 8   10.011.012.013.014.0

I use the SQUEEZE() function to remove duplicate consecutive '|' characters.  Then I re-map the input in a RUN() function.

Thank you.

Paul

Follow me on Twitter

Attachment(s)

Squeeze.zip   7 KB 1 version