sequence

[adams06]

Hi All,

i have two fields

x y
100 03
200 01
300 04
400 08

my problem if i find a value 03 i need to create a 00,01,02
i.e 100 00
100 01
100 02
100 03

if i find a value 01 i need to create a 00
i.e 200 00
200 01

if i find a value 04 i need to create a 00,01,02,03
if i find a value 03 i need to create a 00,01,02 ..07

how can i implement .

Thanks in advance

[DSguru2B]

If its just two columns coming in from the source file then you can do it by building a small routine that takes in two arguments. Something like

Code: Select all

      x = Trim(Arg1)
      y = Trim(Arg2)
      Ans = ""
      For n=0 To y
         Ans = Ans:x:" | ":FMT(n,"2'0'R"):char(010)
      Next n

I have made it pipe delimited. You can change the pipe in the code to whatever delimiter you want to specify.

Another important thing you have to keep in mind is that the second column. If it doesnt have to be of length 2 then remove the FMT(n,"2'0'R") code and just replace it with n. The fmt will work properly untill 99. The moment it hits 100. YOu will get garbaled data for column y.

[adams06]

i have more than one fields. can you tell me how to proceed.

Thanks in advance

If its just two columns coming in from the source file then you can do it by building a small routine that takes in two arguments. Something like

Code: Select all

      x = Trim(Arg1)
      y = Trim(Arg2)
      Ans = ""
      For n=0 To y
         Ans = Ans:x:" | ":FMT(n,"2'0'R"):char(010)
      Next n

I have made it pipe delimited. You can change the pipe in the code to whatever delimiter you want to specify.

Another important thing you have to keep in mind is that the second column. If it doesnt have to be of length 2 then remove the FMT(n,"2'0'R") code and just replace it with n. The fmt will work properly untill 99. The moment it hits 100. YOu will get garbaled data for column y.

[ray.wurlod]

There are various techniques available. Search the forum for "vertical pivot".

[DSguru2B]

So whats going to happen to the other columns. Are they going to be duplicated as well?

[adams06]

Hi DSguru,

The other columns are going to be duplicated.


Thanks in advance.

So whats going to happen to the other columns. Are they going to be duplicated as well?

[DSguru2B]

Well if you would still like to go with my solution then just pass those extra columns that need to be duplicated in the routine.
Say you have 10 Columns. Pass them as 10 arguments to the routine.

Code: Select all

      Col1 = Trim(Arg1)
      Col2 = Trim(Arg2)
       ..
       ..
       ..
       ..
      x = Trim(Arg9) 
      y = Trim(Arg10) 
      Ans = "" 
      OtherColumns = Col1:"|":Col2:"|":.......:Col8
      For n=0 To y 
      Ans = Ans:OtherColumns:"|":x:" | ":FMT(n,"2'0'R"):char(010) 
      Next n 

That should be able to do it.