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

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

Thu, 27 Dec 2012 16:48:54 GMT

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?

swuehl — Thu, 27 Dec 2012 17:01:33 GMT

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?

Fri, 28 Dec 2012 04:25:19 GMT

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?

swuehl — Fri, 28 Dec 2012 12:02:38 GMT

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)

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?

Fri, 28 Dec 2012 12:28:27 GMT

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?

swuehl — Fri, 28 Dec 2012 16:19:06 GMT

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?

Mon, 31 Dec 2012 04:28:37 GMT

1 Data Upload			2nd Data Upload
Contract Id	Position	Random Band	Contract Id	Position	Random Band
1	1	2.574	1	1	2.574
2	1	2.964	2	1	2.964
3	1	1.235	3	1	1.235
4	2	6.751	4	2	6.751
5	2	6.007	5	2	6.007
6	2	4.932	6	2	4.932
7	3	9.457	7	3	9.457
8	3	9.819	8	3	9.819
3rd Upload Data Upload			4th Data Upload
Contract Id	Position	Random Band	Contract Id	Position	Random Band
1	2	6.942	1	2	6.942
2	1	2.964	2	1	2.964
3	1	1.235	3	1	1.235
4	2	6.751	4	2	6.751
5	2	6.007	5	2	6.007
6	2	4.932	6	2	4.932
7	3	9.457	7	3	9.457
8	3	9.819	8	3	9.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?

swuehl — Tue, 08 Jan 2013 16:25:06 GMT

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

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

Fri, 11 Jan 2013 10:12:27 GMT

Dear Swuehl

Many Thanks!

Regards

Rahul