NewRecords n times

7 posts / 0 new
Last post
Andres Alejandro
Offline
Joined: 11/03/2016 - 15:07
NewRecords n times

How can you expand (duplicate, replicate, multiply) n times (tuples) each record on your database?
Example: From current situation to output required.
Current situation 
db.original (matrix [3x5])    

id_field

field1

field2

field3

multiplier_field

R39244

String1

Strin11

String0

5

R39243

String2

Strin12

String0

3

R39247

String5

Strin14

String3

2

 
Output required
 db.modified (matrix [10x5] 

id_field

field1

field2

field3

multiplier_field

R39244

String1

Strin11

String0

5

R39244

String1

Strin11

String0

5

R39244

String1

Strin11

String0

5

R39244

String1

Strin11

String0

5

R39244

String1

Strin11

String0

5

R39243

String2

Strin12

String0

3

R39243

String2

Strin12

String0

3

R39243

String2

Strin12

String0

3

R39247

String5

Strin14

String3

2

R39247

String5

Strin14

String3

2

If anyone knows how to fix my problem, please teach me.  
Regards,
Andrés

Brian Element's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57

Maybe the easiest way is just to do an extraction of your database not putting in a parameter so that you have an exact copy and then append them both together, now you will have duplicate transactions.  You keep doing this until you get the number of copies you want.  Hopefully I am understanding what you want to do if not let me know.

Brian

Andres Alejandro
Offline
Joined: 11/03/2016 - 15:07

Thanks Brian.
Probably, the easiest way is like you told me, but I need to do this job with a original database with #205.864 rows and try to reach the final output with #2.122.472 rows.
Andrés

Brian Element's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57

I was just noticing that your multiplie field is different for some records.  What is your largest multiplier?  Since it is different I would do different extracts, so if the smallest multiplier is 2 then I would append the file to itself.  For 3 I would do an extract of all transactions that have a multiplier of 3 and above, I would then do an extract of all multipliers 4 and above and so on until I have reached the maximum multiplier.  I would then append all these files together.  Since the direct extraction can do up to 50 files hopefully you can create all the files in one pass and then append them together. If this is something you do regularly it shouldn't be too hard to script, you would just need the maximum multiplier and then do a loop to do the extractions and then do an append.

Andres Alejandro
Offline
Joined: 11/03/2016 - 15:07

Nice, I could do that, it is a good solution. But, how to use a counter inside a string or name loop?
max multiplier is 365.
 
For m = 0 To 3
Set db = Client.OpenDatabase("bd-original.IMD")
Set task = db.Extraction
task.IncludeAllFields
dbName = "g_m.IMD"
task.AddExtraction dbName, "", "c_multiplier==3"
task.CreateVirtualDatabase = False
task.PerformTask 1, db.Count
Set task = Nothing
Set db = Nothing
Client.OpenDatabase (dbName)
Next 
*****************************

multiplier
records

3
66404

5
50012

7
46640

8
4984

10
16660

11
700

18
13356

24
160

29
2980

30
536

34
212

84
160

89
324

184
324

240
1072

300
1072

365
268

 

Andres Alejandro
Offline
Joined: 11/03/2016 - 15:07

Very thanks buddy, I solve my last problem with the name string, using m & "name.IMD"
:)
 
 

Brian Element's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57

Glad I could help and you got it working.