PDA

View Full Version : Transpose Grid to Key-Value Pairs



kettle_anonymous
03-09-2006, 05:25 AM
Attachment: concept_load.ktr (http://forums.pentaho.org/archived_att/kettle/concept_load.ktr) Kettle is cool stuff. Clover ETL and Octopus are very limited. Great work!


Here is a trick question or maybe not?



What I am trying to do is the following (Trasmpose GRIDIN to KEVALOUT)







GRIDIN (Excel File)



ATTR1 ATTR2 ATTR3 ATTR4
A B C D
E F G H




KEYVALOUT (Oracle Key-Value pair Table)



KEY VALUE
ATTR1 A
ATTR2 B
ATTR3 C
ATTR4 D
ATTR1 E
ATTR2 F
ATTR3 G
ATTR4 H







Here is what I have tried in KETTLE so far:



1. Feed the Excel (Including Header Field with a assigned row number field)
2. Filter Rows (with the condition row number = 1)
3. Send True Data in Step 2 to a Row Normalizer which transposes the row headers (row number = 1) from the excel file as follows (all these rows are assigned a new field name (which I call ATTR_ID)
ATTR_ID <-- Header/new field name/destined to be my Key field for the row denormalizer step below
ATTR1
ATTR2
ATTR3
ATTR4



4. Send False Data in Step 2 to a Row Denormalizer along with the output of Step 3 above



However I am stuck at this point and cannot proceed to Table Output which ideally would be my next step. I know I am doing this wrong but I dont see any other process that can help me with this. Any help will be greatly appreciated. I have also included the ETL file for review.



Thanks.

kettle_anonymous
03-09-2006, 05:32 AM
My tables did not come out good earlier hence trying again:

GRIDIN (Excel File)



ATTR1 ATTR2 ATTR3 ATTR4

A B C D

E F G H




KEYVALOUT (Oracle Key-Value pair Table)



KEY VALUE

ATTR1 A

ATTR2 B

ATTR3 C

ATTR4 D

ATTR1 E

ATTR2 F

ATTR3 G

ATTR4 H




Also in Step 3:



ATTR_ID <-- Header/new field name/destined to be my Key field for the row denormalizer step below

ATTR1

ATTR2

ATTR3

ATTR4

MattCasters
03-09-2006, 06:30 AM
Hi,

So far you&#39;ve converted an excel sheet into key-value pairs with the row number and type field as unique key.
However, what do you mean by "False" data?
Please note that you only need to read the Excel file once: see §7.3 of the Spoon manual.
If you want to merge 2 streams like you did in the sample concept_load transformation, make sure you supply it with the same row layout from all input streams.
If you want to combine the 2 streams use for example Stream Lookup or a Join (cartesian product) step.

Sorry, but I don&#39;t know what you want to accomplish. Once I know that, the solution would probably be rather straighforward ;-)

Hope this helps,

Matt

kettle_anonymous
03-09-2006, 07:35 AM
The filter rows step provides the option to send 'True' data to one Step and 'False' data to another step.

what I have done is sent the header row to the row normalizer and the data rows to the row denormalizer. basically here is waht I wanted to do:



IN



ATTR1 ATTR2 ATTR3 ATTR4

A B C D

E F G H




OUT



KEY VALUE

ATTR1 A

ATTR2 B

ATTR3 C

ATTR4 D

ATTR1 E

ATTR2 F

ATTR3 G

ATTR4 H



Thanks again.

MattCasters
03-09-2006, 10:16 AM
- In the filter step, specify the condition and the 2 destinations : one for when the condition results to "True", the other for the oposite result.
- For the conversion, use a Normalise step like you planned. There are samples in the Spoon manual to help you along there.

All the best,
Matt

kettle_anonymous
03-09-2006, 03:13 PM
I was under the impression that you would have to DEnormalise to transfrom to key-value. The spoon documentation has good documentation on normalise but no examples on DEnormalise. Anyway I will try to normalise and let you know.

thanks,

Volkan
02-22-2007, 11:46 AM
Hi,
Column to row transpose will be with Row Normaliser step in spoon.

How to this:
1) add an text file input step for your CSV or XLS input file. Sample data as below:

ATR1 ATR2 ATR3 ATR4
A01 B01 C01 D01
E02 F02 G02 H02

2) add Row Normaliser for transposing of data, give a new type field name (FIELDNAME) and give a same name for all column items (FIELDVALUE)

Type field : FIELDNAME

# Fieldname Type New field
1- ATR1 ATR1 FIELDVALUE
2- ATR2 ATR2 FIELDVALUE
3- ATR3 ATR3 FIELDVALUE
4- ATR4 ATR4 FIELDVALUE

3) add Select Values for review of your new structure. Click to button of Get fields to select, see new table as
# Fieldname
001 FIELDNAME
002 FIELDVALUE

4) Now, click to preview icon from menu to see as a result as;

# FIELDNAME FIELDVALUE
001 ATR1 A01
002 ATR2 B01
003 ATR3 C01
004 ATR4 D01
005 ATR1 E02
006 ATR2 F02
007 ATR3 G02
008 ATR4 H02

Good work.. All columns are at a row now.