8 Replies Latest reply: Jan 11, 2013 5:12 AM by Rahul Kale RSS

    How to generate Band range in script, If statement in Script?

      Dear Friends

       

      I want to create a band of random data for the position steps.

       

      for ex:

      Position 1 Data Band 1 to 3

      Position 2 Data Band 4 to 7....

       

      to acheive so i have added an if statement in the excel sheet

       

      IF(A2=1,(RAND()*(3-1)+1),IF(A2=2,(RAND()*(7-4)+4),IF(A2=3,(RAND()*(11-8)+8),IF(A2=4,(RAND()*(15-12)+12),IF(A2=5,(RAND()*(19-16)+16),IF(A2=6,(RAND()*(23-20)+20)))))))

       

      Is it possible to add such statement in the script, so that data, system automatically generate the band depending upon the position uploaded.

       

      Files are enclosed.

       

      Thanks for your help!

       

      Best Regards

      Rahul

        • Re: How to generate Band range in script, If statement in Script?
          Stefan Wühl

          Rahul,

           

          if I understood your request correctly, this should be quite easy:

           

          LOAD Position,

          //     Band,

               IF(Position=1,(RAND()*(3-1)+1),

               IF(Position=2,(RAND()*(7-4)+4),

               IF(Position=3,(RAND()*(11-8)+8),

               IF(Position=4,(RAND()*(15-12)+12),

               IF(Position=5,(RAND()*(19-16)+16),

               IF(Position=6,(RAND()*(23-20)+20))))))) as Band

          FROM

          Band for data.xlsx

          (ooxml, embedded labels, table is DATA);

            • Re: How to generate Band range in script, If statement in Script?

              Hi Swuehl,

               

              Thanks for your reply.

               

              It perfectly meets my requirements! Thanks a million!

               

              Is it possible instead of a random number it generates a unique number in that range?

               

              So that even if refresh the data, the data points are at the same place.

               

              Best Regards

              Rahul Kale

                • Re: How to generate Band range in script, If statement in Script?
                  Stefan Wühl

                  Not sure if I understand:

                   

                  you have got lots of records for one position, do you want to get

                   

                  a) one unique number in the corresponding range (one value for all records with that position)

                   

                  or

                   

                  b) different, randomly distributed values in that range, but not changing when reloading.

                   

                  For a) you just need to state some unique numbers in your load script:

                   

                  If(Position = 1, 1.5633,

                  ....

                   

                  I assume this is not what you want, right?

                   

                  For b) if your number of records and positions are fixed, you can create your table once, then store it into a qvd and just reload this qvd (similar to loading a excel table, data will not change on reload).

                   

                  Or are you talking about something completely different?

                    • Re: How to generate Band range in script, If statement in Script?

                      Hi Swuehl,

                       

                      I am looking for option b (different, randomly distributed values in that range, but not changing when reloading).

                       

                      In my case, the number of records will be changing, also each data which move from position 1 to position 6, during the project time.

                       

                      I am showing them in six stages (scatter chart), but as there are many data points, I have created band option.

                       

                      Please help.

                       

                      Thanks

                       

                      Rahul

                        • Re: How to generate Band range in script, If statement in Script?
                          Stefan Wühl

                          Hm, I am still not sure if I understand. If your number of records change and position will be changed, too, how do you want the band data to not change, then?

                          Could you maybe explain what you mean with some sample lines of data, that will illustrate your expected outcome over time?

                            • Re: How to generate Band range in script, If statement in Script?
                              1 Data Upload2nd Data Upload
                              Contract IdPositionRandom BandContract IdPositionRandom Band
                              112.574112.574
                              212.964212.964
                              311.235311.235
                              426.751426.751
                              526.007526.007
                              624.932624.932
                              739.457739.457
                              839.819839.819
                              3rd Upload Data Upload4th Data Upload
                              Contract IdPositionRandom BandContract IdPositionRandom Band
                              126.942126.942
                              212.964212.964
                              311.235311.235
                              426.751426.751
                              526.007526.007
                              624.932624.932
                              739.457739.457
                              839.819839.819

                               

                              Dear Swehl,

                               

                              Above you will find 4 data upload examples.

                               

                              During 1st Data upload, random numbers are generated in Band (Option b you suggested)

                               

                              Now, what I want is, in the 2nd data upload there is no change in data (position). so it should take the same band number generated earlier. (like Row() funtion).

                               

                              In the 3rd data upload there is a change in position of contract no 1. So it should generate a new random number in that band.

                               

                              However in the 4th data upload if there is no change in data then band number remains same.

                               

                              Is it possible?

                               

                              Thanks

                               

                              Best Regards

                              Rahul

                                • Re: How to generate Band range in script, If statement in Script?
                                  Stefan Wühl

                                  Maybe like this (using a qvd to store and re-use generated random band values for same combinations of Id and Position):

                                   

                                  //this could also be loaded from your input table source (e.g. excel, DB)

                                  INPUT:

                                  LOAD *,

                                  Id &'-'& Position as Key

                                    INLINE [

                                  Id    Position

                                  1    1   

                                  2    1   

                                  3    1   

                                  4    2   

                                  5    2   

                                  6    2   

                                  7    3   

                                  8    3   

                                  ] (delimiter is '\t');

                                   

                                  if isnull(QvdCreateTime('.\bands.qvd'))=-1 then

                                      BANDS:

                                      LOAD Id, Position, Key as BandKey,

                                           IF(Position=1,(RAND()*(3-1)+1),

                                           IF(Position=2,(RAND()*(7-4)+4),

                                           IF(Position=3,(RAND()*(11-8)+8),

                                           IF(Position=4,(RAND()*(15-12)+12),

                                           IF(Position=5,(RAND()*(19-16)+16),

                                           IF(Position=6,(RAND()*(23-20)+20))))))) as Band

                                      Resident INPUT;

                                   

                                  else

                                   

                                      BANDS:

                                      LOAD *

                                       from .\bands.qvd (qvd) where exists(Key, BandKey);

                                     

                                      LOAD Id, Position, Key as BandKey,

                                           IF(Position=1,(RAND()*(3-1)+1),

                                           IF(Position=2,(RAND()*(7-4)+4),

                                           IF(Position=3,(RAND()*(11-8)+8),

                                           IF(Position=4,(RAND()*(15-12)+12),

                                           IF(Position=5,(RAND()*(19-16)+16),

                                           IF(Position=6,(RAND()*(23-20)+20))))))) as Band

                                      Resident INPUT where not exists(BandKey,Key) ;

                                   

                                  endif

                                   

                                  drop table INPUT;

                                   

                                  store BANDS into .\bands.qvd (qvd);

                                   

                                  Hope this helps,

                                  Stefan