Generation Data - Amount

[Michel Laflamme]

Hi,

I generate synthetic data.  I'm looking for a function that allows me to generate a formatted amount like this 999,999,999.99 $.
and aligned to the right

This function doesn't work @string(@randrange(1,99999999999)@,"000,000,000.00 $")@, because it returns this to me :   
000,000,103.82 $
010,753,077.71 $
000,023,089,08 $
and I want
              103.82 $
  10,753,077.71 $
         23,089,08 $

Thanks !

[Scott Schmitz]

You've probably seen that TDM DataGen/DataPainter has modest capabilities for string manipulation.   I'd recommend you feed the string into an "@execsql" command ​and use your favorite SQL commands to replace the leading zeroes with spaces.   I'd also recommend moving the " $" literal outside the "@string"​ function so you'll only worry about numeric/decimal manipulation.    (If you are doing flatfile datagen, then just create a connection profile to the Test Data Repository (gtrep) and use the appropriate Oracle/SQL Server commands.)

[Navin Chari]

Use the formula below:
@randrange(0,999)@,@leftpad(@randrange(0,999)@,0,3)@,@leftpad(@randrange(0,999)@,0,3)@.@leftpad(@randrange(0,99)@,0,2)@ $

[Navin Chari]

Also, to align right you can simulate it by using @leftpad(FORMULA, ,#)

[Michel Laflamme]

Hi Navin,

With this formula : @randrange(0,999)@,@leftpad(@randrange(0,999)@,0,3)@,@leftpad(@randrange(0,999)@,0,3)@.@leftpad(@randrange(0,99)@,0,2)@ $
I cannot generate this amount 103.82 $ or  23,089.08 $ because 2 comas are always included

Thanks

[Navin Chari]

Hi Michael,

Scott's method is pretty straightforward, I didn't realize all the permutations that were required.

If you wish to use the formula I suggested you can create 3 publish lines in the generator:
@randrange(1,999)@,@leftpad(@randrange(0,999)@,0,3)@,@leftpad(@randrange(0,999)@,0,3)@.@leftpad(@randrange(0,99)@,0,2)@ $
@randrange(1,999)@,@leftpad(@randrange(0,999)@,0,3)@.@leftpad(@randrange(0,99)@,0,2)@ $
@randrange(1,999)@.@leftpad(@randrange(0,99)@,0,2)@ $

Cheers,
Navin

[Gregory Messer]

Some good suggestions from Scott and Navin already so I don't think I can add much. I would lean towards Scotts recommendation as you have more control with an
Oracle or SQL Server engine and I don't think that Navins will prevent leading zeros. 

You could try using your original formula or Navins but strip the leading 0's and commas using @lefttrim("@lefttrim(string,0)@",",")@ . You would need to repeat this pattern to cover the complete range of values you could generate.. You will need quote all the strings that contain special characters like commas.

The SQL approach might be slower but cleaner, break the problem down into more manageable chunks by appending section together - 
@execsql(MY_ORACLE_DB, SELECT TO_CHAR( @randrange(1,999999999)@ ,'999,999,999') FROM DUAL)@.@string(@randrange(00,99)@,'00')@ $




​​​

------------------------------
TDM Architect
T-Mobile
------------------------------

[Michel Laflamme]

Hi Gregory,

I create a CA Variable "AMOUNT(min,max,format)" with default value 

@execsql(MY_ORACLE_DB,SELECT SUBSTR(TO_CHAR(@randrange(#min#,#max#)@,#format#),2) FROM DUAL)@.@string(@randrange(00,99)@,"00")@ $

When I want use it into the generator, I indicate :  ~AMOUNT(10,999999999,'999,999,999')~

​​
Thank Gregory, Scott and Navin, that work very good !

Michel