topic Re: Issue with Function >SubField< - what the heck is happening? in App Development

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

BjoernWollny — Thu, 22 Dec 2022 10:46:43 GMT

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 🙂

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

Michiel_QV_Fan — Thu, 22 Dec 2022 15:04:35 GMT

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?

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

MarcoWedel — Thu, 22 Dec 2022 22:25:23 GMT

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.

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

MarcoWedel — Thu, 22 Dec 2022 22:50:42 GMT

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 ];

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

BjoernWollny — Fri, 23 Dec 2022 08:45:04 GMT

Many thanks - that's it

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