The data warehouse we are connecting to has data in tables stored vertically and horizontally. All the ones that are horizontally stored I am having no issues with. However the vertical ones I am.
The load statement is the following (Simplified):
CROSSTABLE(item, TEXT_VALUE, 3)
LOAD
[ID],
[PREV_ID],
[SUB_ID],
[FIELD_NAME],
[TEXT_VALUE]
[NUMBER_VALUE];Currently the data is in this format in the MySQL database.
| ID | PREV_ID | SUB_ID | FIELD_NAME | TEXT_VALUE | NUMBER_VALUE |
| 88492 | 1 | 1 | AMT | | 551 |
| 88492 | 1 | 1 | VALUE | | 125758 |
| 88492 | 1 | 1 | HTS | 5556.55.1000 | |
| 88492 | 1 | 1 | HTS DESC | GOAT FUR | |
I need to make AMT, VALUE, HTS, HTS DESC, have their own columns, with the data associated with them.
Such as
| ID | PREV_ID | SUB_ID | AMT | VALUE | HTS | HTS DESC |
| 88492 | 1 | 1 | 551 | 125758 | 5556.55.1000 | GOAT FUR |
I tried using a CrossTable, but not seeming to get results that would work. Any suggestions on what I should use or how to get this better setup better?
Note PREV_ID links to the KEY in the parent table, and SUB_ID links it to the Part Number associated with these. Some have 50-100 part numbers. So these get huge very quickly. I requested these be split out, but didn't resolve the issue as more empty lines were created which made it even harder as more columns and more blank rows of data with 1 value in AMT, VALUE, HTS, or HTS DESC.
