Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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);
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
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?
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
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?
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
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
Dear Swuehl
Many Thanks!
Regards
Rahul