Using fields and variables to rename fields during load

[#1]

HI

OK, I have a table with columns Ref, M_0, M_1, M_2, M_3, M_Offset and Year

I want to load the rows of the table with field names that are calculated from the M_offset value

i.e. if in the first row, M_Offset = 3, and Year = 2009 then I want to rename M_0 as March_2009, M_1 as April_2009, etc

The next row may have a different offset i.e. 11, in which case M_0 would become November_2009, M_1 = December_2009 and M_2 = January 2010

and so on

Has anyone any ideas?

I have tried using Load M_0 as if(M_offset = 3,March & Year,.........

Thanks in advance

[#2]

You should be able to handle this using SQL commands in your Load statement. Something like this should work:

to_date(M_Offset || '/01/' || M_Year, 'MM/DD/YYYY') As M_0,

add_months (to_date(M_Offset || '/01/' || M_Year, 'MM/DD/YYYY'), 1) As M_1

Note this is Oracle code, I'm not sure if you would have to modify it if you were using TSQL.

You could use a to_char(..., 'MON YYYY') or something similar around the fields to get a string like in your example. If you keep the straight dates, you can format them in QlikView using:

Month (M_0) & ' ' & Year(M_0)

[rwunderlich]

Just to confirm, you want to change the column names on a row-by-row basis? How many rows are you talking about?

-Rob

[#4]

Take a look in the documentation at page 333 at the RENAME FIELD (and RENAME TABLE) script commands. These do what you would expect... rename fields and tables. Even COOLER, however, is that you can use a mapping table to do the rename. So you do a MAPPING LOAD with all the "strangely named fields" and all the "english named fields" and do the translation automatically. And, if you're really good, you can use autogenerate() to automatically generate the mapping table, because the field names look to be pretty easy to map between "strange" and "english".

-A

[rwunderlich]

Anthony Deighton wrote:
... is that you can use a mapping table to do the rename. <div></div>

I understood the original post to say that in row 1, M_0 would be named March_2009 and in row 2 M_0 should be renamed November_2009. So it's not a straight RENAME of M_0 to the same name for all rows.

If I understand the question correctly, here's a possible solution. You didn't say if you want all the columns in the same table. If you want seperate tables, just pull out the CONCATENATE keyword in the load. I've attached an example qvw also.

tempdata:
LOAD * INLINE [
Ref, M_0, M_1, M_2, M_3, M_Offset, Year
1, 201, 301, 401, 501, 3, 2009
2, 202, 302, 402, 502, 11, 2009
3, 203, 303, 403, 503, 1, 2008
4, 204, 304, 404, 504, 12, 2009
]
;
idx:
LOAD DISTINCT
 M_Offset as idx_offset,
 Year as idx_year
RESIDENT tempdata
;
Sub loadCols (month, year)
LET basedate = MakeDate(year, month);
LET m0 = Date(basedate, 'MMMM_YYYY');
LET m1 = Date(AddMonths(basedate,1), 'MMMM_YYYY');
LET m2 = Date(AddMonths(basedate,2), 'MMMM_YYYY');
LET m3 = Date(AddMonths(basedate,3), 'MMMM_YYYY');
final:
$(CONCATENATE) LOAD
 Ref,
 M_0 as $(m0),
 M_1 as $(m1),
 M_2 as $(m2),
 M_3 as $(m3)
RESIDENT tempdata
WHERE M_Offset = $(month) AND Year = $(year)
;
End Sub
SET CONCATENATE = ''; // No concat on first call
For i = 0 to NoOfRows('idx')-1
 Call loadCols (peek('idx_offset', i, 'idx'), peek('idx_year', i, 'idx'))
 SET CONCATENATE = 'CONCATENATE';
Next
DROP TABLES tempdata, idx;

I think this will work for a reasonable number of rows.

-Rob

[#6]

Hi NMiller

Thanks, but I'm not so expert as to convert Oracle code into the QV

I did solve the problem with "preconditioning" in excel, and I have some other options, so I may try this when I have time

Thanks agan

;o)

[#7]

Hi Rob

- Yes

Start Position in Excel:

Column Headers:

Offset_Month, Year, M0, M1, M2, M3

Data

4,2009,1,2,3

3,2009,4,5,6

10,2009,7,8,9

To convert to:

Headers:

Jan_2009,Feb_2009,Mar_2009,Apr_2009,May_2009,Jun_2009,Jul_2009,Aug_2009,Sep_2009,Oct_2009,Nov_2009,Dec_2009,Jan_2010

Data:

0,0,0,1,2,3,0,0,0,0,0,0,0

0,0,4,5,6,0,0,0,0,0,0,0,0

0,0,0,0,0,0,0,0,0,0,7,8,9

- I am talking about less than 100 rows...

🐵

[#8]

Hmmm...

Sounds good, I'll start with the mapping load and carry on

[#9]

Yup! Works! - does exactly what I wanted

but what to do for 100 rows - .....?.......!

I'll apply some engine newity this end

Ta!

[#10]

Actually, as this is only for a 12 month rolling forecast, I oly need to go to M_12 so Not so bad!