Processing a matrix-like structure

[yceken]

You have a CSV file that has the following matrix structure:

Prod, Week1, Week2, Week3, Week4
P1, 1, 2, 3, 4
P2, 2, 3, 4, 5
.., .., .., .., ..
.., .., .., .., ..
.., .., .., .., ..
Pn, 6, 7, 8, 9

The task is to insert these rows to an Oracle 9i database on a Sun box (Solaris 8) as follows (basically normalizing the data):

P1, Week1, 1
P1, Week2, 2
P1, Week3, 3
P1, Week4, 4
P2, Week1, 2
...
...
...
Pn, Week4, 9

What would be the best way to process this type of files using Mercator 6.5. Has anyone encountered this type of input files before?

Any input, ideas are welcome.

Thanks a lot.

Yilmaz

 

[BocaBurger]

Couple of ideas. You could read in the input file in more than one card and use rules to sort and index the data.
You could also read in the data and create sorted two output files that could be read in and then passed to the DB. You will have to use Oracle 8 adapter or ODBC since 6.5 does not support Oracle 9 clients.



BocaBurger
<===========================||////////////////|0
The pen is mightier than the sword, but the sword hurts more!

 

[jmanj]

Read 1 record and output 4 records(per week) into the same file.

 

[yceken]

Thanks a lot for the recommendations. But, I want to make this a little bit more challenging by adding the following items:

- the number of fields after the first field (Prod) is not known, but is the same for all records in the input file

- the number of fields after the first field (Prod) can be 4 in input_file_1 while it can be 8 in input_file_2, so dynamic it is.

- The Week_n value from the header record should be transformed to any valid calendar date that is in that week and should be part of the database record.

Any ideas for attacking the dynamic number of input columns?

Thanks,
Yilmaz

 

[BocaBurger]

How are you going to dynamically create columns in the database to handle the data?

This is mostly type tree design. You have (s) # of fields in the group, but you will need a definite terminator and/or identifier for the next group.

Without knowing what the data for the Week_n looks like, it is hard to tell what date function would be needed.

Try the advanced mapping course offered by Ascential.



BocaBurger
<===========================||////////////////|0
The pen is mightier than the sword, but the sword hurts more!

 

[yceken]

BocaBurger,

Database table has a static structure:

(Prod_Cd, Date, $_Value)

If input_file_1 contains 4 columns after prod_cd, then the transformation should insert 4 rows to the table. On the other hand, the transformation should insert 8 records to the same table if the there are 8 columns after the prod_cd in input_file_2.

I totally agree with your comments with the terminator characters and type tree design, but the dynamic nature of this makes it difficult.

Is there a way to do the following: define two groups for each record: Group1=Prod_cd and Group2=Rest of the line and try to parse Group2 into individual values and insert to database?

For the Week_n thing, I think there should be a function that can map Week2 to 01/10/2005 (assume the rule is to find the first business day of that week, and assume the current year is 2005). Am I right?

Thanks,
Yilmaz

 

[BocaBurger]

Yes, if you pass the information into a functional map $_value times, you will get a row for each with the same data for the first 2 fields and $_value different for each.

I don't know of an internal date function that can determine business week, when there are some holidays that actual fall one one day and are celebrated on another. Also, product has international use and holidays tend to vary. You might just want to create a lookup table for the week # and date and add that as an input card.



BocaBurger
<===========================||////////////////|0
The pen is mightier than the sword, but the sword hurts more!