Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
i was searching in the reference guide for some expression that reads the fieldname like other expressions read the content of the field.
f.e. thre are filds like building1, building2, building3
the goal is, to change the format from
building1, value
23, temp
80, moist
to
building, b_number, value
23, 1, temp
80, 1, moist
is it possible to read these like
right(anyfildname,1) to get the numbers (1,2,3 ec.) ?
if so, it could be possible to used in an expression like this:
LET b_number = right_fieldnames(1);
OR
LET b_number =
IF (left_fieldname(5)='build', right_fieldnames(1));
is something like this possible ?
Not quite sure I understand... when you write your Load statement, then you know the field names, so you might as well hard-code the number in the script. Or what am I missing?
Anyway, if you loop over the fields, then you can use the FieldNumber() and FieldName() functions, and in your case it could then be
Right (FieldName(2, 'Table'),1) as Building
Hi, Alexander
You can achieve this using cross table.
I wrote a sample script as an example. In this piece of code, I wrote a table using inline and store, but you can start from the second step, using crosstable on your original tables:
//Creation of Test Table
Test:
LOAD * INLINE [
STUDY, TYPE, BUIDING1, BUILDING2, BUILDING3
1, A, 200, 300, 400
1, B, 100, 50, 20
1, C, 15, 90, 400
2, A, 15, 32, 200
2, B, 200, 100, 150
2, C, 50, 100, 70
];
Store Test into Test.qvd;
drop table Test;
//
//QVDs will be read to simulate a source file
//Cross table could be done in the original Load Inline
tempBuildings:
CrossTable(tempBuilding, Value, 2)
LOAD STUDY,
TYPE,
BUIDING1,
BUILDING2,
BUILDING3
FROM
[Test.qvd]
(qvd);
//At this point tempBuildings has the Fields: STUDY, TYPE, tempBuilding and Value
Building:
Load STUDY,
TYPE, Right(tempBuilding,1) as Building,
Value
resident tempBuildings;
drop table tempBuildings;
Hope this helps,
Erich
Yes it is possible. See attachment. You should look in the help at the Crosstable statement and the string functions.
Im sorry, this was a missunderstanding.
the example i provided was a bit misleading.
im not looking for a crosstable statement or a string function. this wouldnt be the problem (there are tons of examples for this here)
im looking for something like
"Right(tempBuilding,1) as Building,"
but the source should be the field name (tempBuilding) and not the content.
something that just reads the number from the name of the field
LET Numfield= Right(field7,1)
like
Right(test_table,1) , so its gets the last digit (or char) of every fieldname in "test_table"
im sorry for the missunderstanding.
Not quite sure I understand... when you write your Load statement, then you know the field names, so you might as well hard-code the number in the script. Or what am I missing?
Anyway, if you loop over the fields, then you can use the FieldNumber() and FieldName() functions, and in your case it could then be
Right (FieldName(2, 'Table'),1) as Building