Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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

View solution in original post

8 Replies
swuehl
MVP
MVP

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);

Not applicable
Author

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

swuehl
MVP
MVP

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?

Not applicable
Author

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

swuehl
MVP
MVP

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?

Not applicable
Author

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

swuehl
MVP
MVP

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

Not applicable
Author

Dear Swuehl

Many Thanks!

Regards

Rahul