Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

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

Tags (3)
1 Solution

Accepted Solutions
MVP
MVP

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

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

8 Replies
MVP
MVP

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

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

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

Highlighted
MVP
MVP

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

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

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

MVP
MVP

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

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

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

MVP
MVP

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

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

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

Dear Swuehl

Many Thanks!

Regards

Rahul