1 reply to this topic

[Ralph-Steven Wedemeyer]

Hello and good evening everybody.

Hopefully there is no limit to threads per month, but I do have another question on an issue I cannot resolve by myself.

Actually, I thought I had already seen a similar post and probably discussed this myself, but I was not able to find something fitting. But please feel free to direct me in case I just missed something.

The issue illustrated:
We now get the USUBJID in a project, e.g. UK_0111. The client now requests, that the subject ID is extracted from this and delivered as a 4-digit term.
In my example, the ID has to be "0111" to allow the import to the data base.

Using a custom transformation with >right(USUBJID, 4)< in this specific case does not work, as the outcome/new column now contains "111" instead of "0111" (defined as a number and not as a text).

I then tried the combination >format(right(XXX), 'ABC')< or even >format(right(XXX), '00000000')< remembering the way for such an issue in Excel (yes, we had a workaround for this issue back in 5.3, but did not need it for Phoenix until now).
But the latter didn't work at all and the first gave back ABC in each cell, which I did not expect as 'ABC' is literally found in the manual to define a text entry.

Long story short: I am lost. Is there a way to gain a leading zero?

Best regards,

Steven

Edited by Simon Davis, 28 October 2014 - 06:15 AM.
restore formatting lost after migration

[Simon Davis]

Hi Ralph, sorry for the delay with the migration of the forum, taking some holiday etc. it seems your question was overlooked.  The short answer is this is difficult currently since every time phoenix sees a number it converts that cell to a numeric cell.  

 

I have a couple of workarounds to propose

 

1) add a row that is a string and irrelevant to analysis (e.g. dummy) this forces the output to be text (unless the result is a full number) then you can use the right function or substitute.  This could work well for the user if all your ID has a leading 0 but if some don’t have a leading zero then he will see a mixture of numeric and text.  If the ‘dummy row’ is blank out, the column would become numerical and the leading 0s will disappear.  Please see attached to understand what I am talking about.

 

However I appreciate the dummy row of data might not be acceptable in GxP, so personally I would probably do this at the moment,

 

2)  First I truncated to the numeric IDs I wanted and made it that those values with leading zeros have a character value instead of the leading zero, in this case an O.

 

Then when I used REPLACE (within the filter) > since I am updating a column that already exists then the text property persists and I can replace O111 with 0111.

 

The down side is that you would need to write a series of replaces for *every* ID with a leading zero, within the Filter object of a DataWizard, since I think if you were to do the replace with a Column transform then you’re back to the problem of the newly created column stripping leading Zeros.

What do you think? Although it has an essentially manual step it does at least end up in the summary sheet for auditing etc.

(also you have this right and left-alignment in Phoenix however when exporting to e.g. CSV or to a Table this of course would not be an issue.)

 

  Simon