Issue with Function >SubField< - what the heck is happening?

[BjoernWollny]

Hi,

I am totally lost. Often I used SubField function and it always worked as it was supposed to be. But this time I do not have any idea, why I won't get the expected results.

I have a field that is seperated by '-'. I am especially interested in the fifth subfield, so I am using formula

SubField(Field, '-', 5) as SubField5

Field value is in this example (screenshot below) 'DE-1110-F-CF-00E75-07' so I expect as result from this formula '00E75'

But as you can see, the function returns '00' 

I tried to recreate the formula as Dimension, but still it shows '00' 
Very strange is, that function is working, when I am using a measure - eventhough there is just one entry for this dataset as you can see from the count (last column).
So honestly I am totally lost, why function in script is not working properly.

The laod script is very straight forward:

Load	
    rowno() as RowID,
    ILOAN as SourceID,
    TPLNR as Field,
    SubField(TPLNR, '-', 3) as SubField3,
    SubField(TPLNR, '-', 4) as SubField4,
    SubField(TPLNR, '-', 5) as SubField5
FROM [lib://*****/ILOA.qvd]
(qvd);

Do you have any idea, about the source of this issue and maybe how to fix it? What could have gone wrong?

Really appreciate your support and many thanks in advance for feedback 🙂

[Michiel_QV_Fan]

I duplicated your load and didn't encounter an issue using inline:

data:
Load * Inline [
PK, data
A1, DE-1110-F-CF-00E75-07
];

Left Join (data)
Load PK,
rowno() as RowID,
'ILOAN' as SourceID,
'TPLNR' as Field,
SubField(data, '-', 3) as SubField3,
SubField(data, '-', 4) as SubField4,
SubField(data, '-', 5) as SubField5
Resident data;

That confirms the function works in Sense but not an answer to your issue.

How does that specific value look like in the QVD? Might be a load error and that a different token is in the qvd?

[MarcoWedel · Accepted solution]

I guess your subfield is interpreted as number in exponential notation while there had already been loaded another zero value (with format 00) before the one in question.
Try using

Text(SubField(TPLNR, '-', 5)) as SubField5

instead.

[MarcoWedel]

one example to illustrate:

table1:
Load	
    rowno() as RowID,
    ILOAN as SourceID,
    TPLNR as Field,
    SubField(TPLNR, '-', 3) as SubField3,
    SubField(TPLNR, '-', 4) as SubField4,
    SubField(TPLNR, '-', 5) as SubField5,
    If(IsText(SubField(TPLNR, '-', 5)),'IsText',If(IsNum(SubField(TPLNR, '-', 5)),'IsNumber')) as TextOrNumber,
    Text(SubField(TPLNR, '-', 5)) as SubField5Text
Inline [
ILOAN, TPLNR
ILOAN1, DE-1110-F-CF-00-07
ILOAN2, DE-1110-F-CF-00A75-07
ILOAN3, DE-1110-F-CF-00E75-07
ILOAN4, EF-1111-G-DG-00F75-08
ILOAN5, FG-1112-H-EH-00G75-09
ILOAN6, GH-1113-I-FI-00I75-10
];

[BjoernWollny]

Many thanks - that's it


Thank you both a lot for taking your time and supporting me on this issue! Really appreciate 🙂